information provided by dates

jwgreen1986

Board Regular
Joined
Mar 8, 2019
Messages
64
ill try to keep this simple. basically i have a start date and an end date of an employee.

the first thing i want to do is work out how many days a person has worked over a quarter. so i need something that will work out how many days there are in jan feb and mar and add these together. then do the same for apr may jun and so on.

next i want to look at a persons start date and work out how many of the days they have worked in a quarter. for example, if they started on 5th feb there pro rate would be 23+31 which equals 54 days out of 89 (jan - mar).
i would also like to do the same but with an end date. so if someone leaves the company id like to know how many days they have worked before they have left.

the next thing i would like to do is populate some month fields. so i would have the 3 months of the quarter listed. then looking at their start date and end date i would like to simply put yes or no in the month cell for each employee as to whether or not they were working for the company in said month. for example if an employee started working in the company on 17th feb they would have no in jan but yes in feb and mar. again i would then like to do the same for leaving date.

this has really got me stumped. im trying to think of formulas i can use but can think of any. is there a simple way of doing it, or will i need to change the start/end date to a different format to work out the days they have worked?

any help would be appreciated
 
Last edited:
Getting #Value ! when using the formula. below is what i have
=IF(AND(M6>EDATE(W$5,-2)),MAX(0,MIN((M6-L6),EOMONTH(W$5,0)-L6)+1)-SUM(V6:V6),0)

M6 is the end date on my spreadsheet
W5 is the newly created row called Mar-18
L6 is the start date
v6 is a blank new row needed.
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
difficult to check but your formula is a mixture of absolute and relative reference, at best it will only work on one cell and it won't work if copy to others.

could you post your data here or share it on Google drive or similar
 
Upvote 0
it has worked on a couple of people, but only those with an end date. it has worked out the days worked between their start date and end date instead of how many days they have worked in the quarter.
 
Upvote 0
ok so this is how it is. ive left out the cells that are not relevant B-J etc. i have noticed a few issues. one of them has no no no for the months work despite them having a start date of 01/10/2016 and no end date. i have also used one as an example where it is giving me a total amount of days worked but it is working out the difference from their start and end dates, not their quarter totals. the majority of the other cells have #Value ! apart from any that has an end date. once again any that has an end date is working the difference from start to end dates and not how many days they have done in a quarter.

row W is where i want the totals for days worked in the quarter to be added up.

W6 has =IF(AND(M6>EDATE(W$5,-2)),MAX(0,MIN((M6-L6),EOMONTH(W$5,0)-L6)+1)-SUM(V6:V6),0)
the active months has =IF(AND(L6<EOMONTH($U$5,0),M6>=$U$5),"YES","NO")

The active month is working on all others apart from this one value of 01/10/2016. it is in the same format as all other dates as well and comes from the same source.

[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]L[/TH]
[TH="align: center"]M[/TH]
[TH="align: center"]R[/TH]
[TH="align: center"]S[/TH]
[TH="align: center"]T[/TH]
[TH="align: center"]U[/TH]
[TH="align: center"]V[/TH]
[TH="align: center"]W[/TH]
[TH="align: center"]K[/TH]
[TH="align: center"]L[/TH]
[TH="align: center"]M[/TH]
[TH="align: center"]N[/TH]
[TH="align: center"]O[/TH]
[TH="align: center"]P[/TH]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]Name[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Jan-19[/TD]
[TD="align: right"]Feb-19[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Mar18b-19[/TD]
[TD="align: right"]Mar-19[/TD]
[TD="align: right"]Apr-19[/TD]
[TD="align: right"]May-19[/TD]
[TD="align: right"]Jun-19[/TD]
[TD="align: right"]Jul-19[/TD]
[TD="align: right"]Aug-19[/TD]
[TD="align: right"]Sep-19[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Employee 1[/TD]
[TD="align: right"]20/06/2015[/TD]
[TD="align: right"]10/02/2019[/TD]
[TD][/TD]
[TD="align: right"]YES[/TD]
[TD="align: right"]YES[/TD]
[TD="align: right"]NO[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]4984[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]Employee 2[/TD]
[TD="align: right"]01/10/2016[/TD]
[TD="align: right"][/TD]
[TD]Start[/TD]
[TD="align: right"]NO[/TD]
[TD="align: right"]NO[/TD]
[TD="align: right"]NO[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
i am not just getting the value of 0 in all of the cells. i have changed the referencing for BY5 to W5 to match the cell. the cells are in general format. i dont need the previous quarters or whole rows of months. just basically the 3 months in a quarter. then each quarter i was hoping i could change these to apr-19, may19 and jun - 19 and it would automatically refresh.

what i now have is

=IF($M6>EDATE(W$5,-2),MAX(0,MIN(($M6-$L6),EOMONTH(W$5,0)-$L6)+1)-SUM($V6:VX6),0)
 
Upvote 0
i am not just getting the value of 0 in all of the cells. i have changed the referencing for BY5 to W5 to match the cell. the cells are in general format. i dont need the previous quarters or whole rows of months. just basically the 3 months in a quarter. then each quarter i was hoping i could change these to apr-19, may19 and jun - 19 and it would automatically refresh.

what i now have is

=IF($M6>EDATE(W$5,-2),MAX(0,MIN(($M6-$L6),EOMONTH(W$5,0)-$L6)+1)-SUM($V6:VX6),0)

red above is not right, too many columns
 
Upvote 0
changed. still getting #value ! apart from those with an end date and it is still working out the difference between start date and end date.

do i need to have anything enabled in excel or a certain format?
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top