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

 

[GR00007]

Similar questions have been asked before; from using the search feature these were found.
https://www.mrexcel.com/forum/excel-questions/431558-calculating-number-days-left-quarter.html
https://www.mrexcel.com/forum/excel...highlight=calculate+days+worked+month+quarter
https://www.mrexcel.com/forum/excel...highlight=calculate+days+worked+month+quarter

You can probably find more.

 

[AlanY]

Row 2 - True is month at work
Row 3 - True is Start month
Row 4 - True is End month

Row 5 - Work days in month
Row 6 - Work days in quarter

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Start	End		Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19
2	Employee 1	02/12/2018	20/07/2019	In work	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE
3				Start	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
4				End	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE
5		work days (month)					30	31	28	31	30	31	30	20
6		work days (quarter)					30			90			91			20
Sheet4

Cell Formulas
Range	Formula
E2	=AND($B2E$1,0),$C2>=E$1)
E3	=AND(D2=FALSE,E2)
E4	=AND(E2,F2=FALSE)
E5	=IF(AND(E2,F2),IF(E3,EOMONTH(E1,0)-$B2+1,DAY(EOMONTH(E1,0))),IF(AND(E4,F4=FALSE),DAY($C2),""))
E6	=IF(MOD(MONTH(E1),3)=0,SUM(C5:E5),"")

 

[jwgreen1986]

this is absolutely fantastic and exactly what i was looking for. just one little question with regards to the formula for E5. is there any way that this can be summed so it is in one cell. i like how you have it spread out per month, but im wanting it in one cell (just for the total days worked) so it equals up all the days for the quarter that a person has worked based on their start date

 

[jwgreen1986]

so instead of it working out the days worked month by month, is there a way it will just show the overall days worked. i understand that is what E6 does, but is there a way to get to this without the need of E5, or by only having one formula?

 

[AlanY]

take your picks

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Start	End		Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19
2	Employee 1	02/12/2018	20/07/2019	In work	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE
3				Start	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
4				End	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE
5		work days (month)		231			30	31	28	31	30	31	30	20
6		work days (quarter)		231			30			90			91			20
Sheet4

Cell Formulas
Range	Formula
D5	=SUM(E5:P5)
D6	=C2-B2+1

 

[jwgreen1986]

how do i put a table on to explain a little clearer
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Start	End		Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19
2	Employee 1	02/12/2018	20/07/2019	In work	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE
3				Start	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
4				End	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE
5		work days (month)		231			30	31	28	31	30	31	30	20
6		work days (quarter)		231			30			90			91			20

<thead>
</thead><tbody>
</tbody>

</body>

 

[AlanY]

i think i know what you're after, but i'm off now.
i can have a look tomorrow if you still haven't found a solution

 

[jwgreen1986]

so this is what mine looks like roughly. the true/false part works fine on mine and was just what i wanted. with regards to the days worked i want it in one column like this called pro rata. so if they started before the first day of a quarter (01/01/2019) and have not left the company then they have worked the full time. if they have started after the first day of the quarter then work out how many days they have worked up until the end of the quarter (31/03/2019).
if they have left the company then work out how many days they worked between the start of the quarter and their end date. im thinking i am going to need a quarter start date and quarter end date somewhere so i can change these each quarter for future reports. its just the calculation i cant figure out to give me the days they have workedin the quarter. i was hoping to avoid expanding the spreadsheet further because it already has a lot of info on it. hence wanting a formula to just put it in one cell.

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Start	End		Jan-19	Feb-19	Mar-18	Pro Rata9	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19
2	Employee 1	02/12/2018		In work	TRUE	TRUE	TRUE	??????	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE
3	Employee 2	03/02/2019		Start	FALSE	TRUE	TRUE	??????	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
4				End	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE
5		work days (month)		231			30	31	28	31	30	31	30	20
6		work days (quarter)		231			30			90			91			20

<tbody>
</tbody>

[/QUOTE]

 

[AlanY]

the work days per quarter are on the right side of the sheet
as its stand an empty column between the 2 sets of data (Col P) is needed

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		Start	End	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19		Dec-18	Mar-19	Jun-19	Sep-19	Dec-19
2	Employee 1	17/01/2019	30/06/2019				Yes	Yes	Yes	Yes	Yes	Yes					0	74	91	0	0
3	Employee 2	19/10/2018	05/09/2019	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes		74	90	91	67	0
4	Employee 3	18/12/2018	30/08/2019			Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes			14	90	91	61	0
Sheet4

Cell Formulas
Range	Formula
D2	=IF(AND($B2D$1,0),$C2>=D$1),"Yes","")
Q2	=IF(AND($C2>EDATE(Q$1,-2)),MAX(0,MIN(($C2-$B2),EOMONTH(Q$1,0)-$B2)+1)-SUM($P2:P2),0)