information provided by dates

[jwgreen1986]

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

 

[jwgreen1986]

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.

 

[AlanY]

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

 

[jwgreen1986]

hi, i dont have google drive unfortunately.

 

[jwgreen1986]

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.

 

[AlanY]

can you try to paste it like post #9

 

[jwgreen1986]

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]

 

[AlanY]

i've use your data in the linked file, have a look to see if you can make sense of it.
the 2 master formula are in S6 & BY6, also note that you need a end date (even a dummy date in the future) for it to work

https://drive.google.com/file/d/1YHXVhCU26N3P2GKhh1aOqbtc4AcOXWO5/view?usp=sharing

 

[jwgreen1986]

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)

 

[AlanY]

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

 

[jwgreen1986]

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?