Employee Time Off Tracking (among other items)

[rallysport096]

Hey everyone,

I have a spreadsheet that lists my employees' work hours, including vacation, sick, etc. and keeps track of the balances and so on. I'm having difficulty trying to set up using a range of dates as the criteria for showing me information that could span a different range of dates....

Confused? Me too. I wish I could explain this better to make my Google searches easier.

So, I track everything on a Mon-Sun weekly basis. Near the top of my sheet I have a section with 52 rows which shows the work weeks for the entire year. Within that, the work weeks have "FROM" and "TO" columns (i.e. 7/8/19 | 7/14/19) Below that, I have an area for time off, which has "FROM" and "TO" columns for the time off dates, "TYPE" for the type of PTO requested (Sick, Vacation, etc), a few other columns, then the "# of Days" and "Total Hours" columns.

If I have an employee take off multiple days (Let's say Friday 7/12/19 thru Wednesday 7/17/19), I need to be able to show on my weekly list the total number of hours the employee is taking off. Now, I know the NETWORKDAYS function, and that gives me the number of work days (we're not open Sat and Sun) between the dates, but since the time off is over a weekend, I need my weekly list to see that there's 1 PTO day (or X amount of hours) for the week of 7/8-7/14 and 3 PTO days for the week of 7/15-7/21.

I guess the main thing I'm having trouble with, overall, is how to best use excel to find information using a range of dates WITHIN another range of dates (finding 7/10-7/12 within 7/8-7/14 and figuring out the values).

I hope it makes sense, and hope someone can point me in the right direction. Thank you so much for the help!

 

[Aryatect]

Hi, If I understood correctly below should work:

Book1
	A	B	C	D	E	F
1	From	To	No of days off		Leave from	Leave to
2	7/8/2019	7/14/2019	1		7/12/2019	7/24/2019
3	7/15/2019	7/21/2019	5		7/26/2019	8/2/2019
4	7/22/2019	7/28/2019	4
5	7/29/2019	8/4/2019	5
6	Monday	Sunday
Sheet1

Cell Formulas
Range	Formula
C2	{=SUM((IF(WORKDAY.INTL(B2,-1,1)<$F$2:$F$3,WORKDAY.INTL(B2:B2,-1,1),$F$2:$F$3)-IF(A2>$E$2:$E$3,A2:A2,$E$2:$E$3)+1)*(B2>=$E$2:$E$3)*(A2<=$F$2:$F$3))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[rallysport096]

Hi, If I understood correctly below should work:

	A	B	C	D	E	F
1	From	To	No of days off		Leave from	Leave to
2	7/8/2019	7/14/2019	1		7/12/2019	7/24/2019
3	7/15/2019	7/21/2019	5		7/26/2019	8/2/2019
4	7/22/2019	7/28/2019	4
5	7/29/2019	8/4/2019	5
6	Monday	Sunday

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula C2 {=SUM((IF(WORKDAY.INTL(B2,-1,1)<$F$2:$F$3,WORKDAY.INTL(B2:B2,-1,1),$F$2:$F$3)-IF(A2>$E$2:$E$3,A2:A2,$E$2:$E$3)+1)*(B2>=$E$2:$E$3)*(A2<=$F$2:$F$3))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thanks for this. However, it's not yielding any results. The data, or sum result, I'm trying to pull would come from Column C in your example, but nothing in your formula pulls the info. I guess I left out I have a column that converts the # of days into worked hours (8, in this case), which I guess you could use Column D, in the example.

And just to clarify, as I see nothing in the formula referencing cells E3:E5 and F3:F5, nor anything past row 3, would the "$F$2:$F$3" be used for the rest of the column? (meaning, could it be used as "$F$2:$F$5")

Last thing, I have another column that specifies the leave (i.e. sick vs. vacation time), so I currently use an "IF" function for that. I'm sure I can figure out how to work it in but I don't have much experience with arrays and wanted to see if you knew of any possible issues with the IF function.

Thanks again!

 

[Aryatect]

Thanks for this. However, it's not yielding any results. The data, or sum result, I'm trying to pull would come from Column C in your example, but nothing in your formula pulls the info. I guess I left out I have a column that converts the # of days into worked hours (8, in this case), which I guess you could use Column D, in the example. - Right now it is pulling the number of days someone has taken leave in a range, If required you can simply add *8 at the end of the formula to get hours.

And just to clarify, as I see nothing in the formula referencing cells E3:E5 and F3:F5, nor anything past row 3, would the "$F$2:$F$3" be used for the rest of the column? (meaning, could it be used as "$F$2:$F$5") - Here the data was only in E2:E3 & F2:F3, this range can be expanded as shown below.

Last thing, I have another column that specifies the leave (i.e. sick vs. vacation time), so I currently use an "IF" function for that. I'm sure I can figure out how to work it in but I don't have much experience with arrays and wanted to see if you knew of any possible issues with the IF function. - Below should work

Thanks again!

Book1
	A	B	C	D	H	I	J
1	From	To	No of days off	Reason	Leave from	Leave to	Type
2	7/8/2019	7/14/2019	8	Sick	7/12/2019	7/24/2019	Sick
3	7/15/2019	7/21/2019	40	Sick	7/26/2019	8/7/2019	PTO
4	7/22/2019	7/28/2019	32	Sick, PTO	9/3/2019	9/4/2019	Vacation
5	7/29/2019	8/4/2019	40	PTO
6	8/5/2019	8/11/2019	24	PTO
7	8/12/2019	8/18/2019	0
8	8/19/2019	8/25/2019	0
9	8/26/2019	9/1/2019	0
10	9/2/2019	9/8/2019	16	Vacation
11	9/9/2019	9/15/2019	0
Sheet1

Cell Formulas
Range	Formula
C2	{=SUM((IF(WORKDAY.INTL(B2,-1,1)<$I$2:$I$10,WORKDAY.INTL(B2,-1,1),$I$2:$I$10)-IF(A2>$H$2:$H$10,A2,$H$2:$H$10)+1)*(B2>=$H$2:$H$10)*(A2<=$I$2:$I$10))*8}
D2	{=TEXTJOIN(", ",1,IF((B2>=$H$2:$H$10)*(A2<=$I$2:$I$10)*(ROW($J$2:$J$10)-ROW($J$2)+1),$J$2:$J$10,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[rallysport096]

	A	B	C	D	H	I	J
1	From	To	No of days off	Reason	Leave from	Leave to	Type
2	7/8/2019	7/14/2019	8	Sick	7/12/2019	7/24/2019	Sick
3	7/15/2019	7/21/2019	40	Sick	7/26/2019	8/7/2019	PTO
4	7/22/2019	7/28/2019	32	Sick, PTO	9/3/2019	9/4/2019	Vacation
5	7/29/2019	8/4/2019	40	PTO
6	8/5/2019	8/11/2019	24	PTO
7	8/12/2019	8/18/2019	0
8	8/19/2019	8/25/2019	0
9	8/26/2019	9/1/2019	0
10	9/2/2019	9/8/2019	16	Vacation
11	9/9/2019	9/15/2019	0

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula C2 {=SUM((IF(WORKDAY.INTL(B2,-1,1)<$I$2:$I$10,WORKDAY.INTL(B2,-1,1),$I$2:$I$10)-IF(A2>$H$2:$H$10,A2,$H$2:$H$10)+1)*(B2>=$H$2:$H$10)*(A2<=$I$2:$I$10))*8} D2 {=TEXTJOIN(", ",1,IF((B2>=$H$2:$H$10)*(A2<=$I$2:$I$10)*(ROW($J$2:$J$10)-ROW($J$2)+1),$J$2:$J$10,""))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thanks so much. I realized the earlier problem I was having was applying this to over 50 cells using CTRL+ENTER, and realized the array doesn't apply dynamically (am I saying that right?)

Any suggestions on applying it to a 50-cell column easily (on 14 different sheets?)

 

[rallysport096]

Ok... I don't know why it took me so long to figure out we we're going down a wrong path. Maybe I can better explain this...

I have a table that has information for normal employee hours worked, etc... It looks like this:

i	A	B	C	D	E	F
1	Date From	Date To	Reg Hours	Vac Hours	Sick Hours
2	1/7/2019	1/13/2019	40		8
3	1/14/2019	1/20/2019	38	16
4	1/21/2019	1/27/2019	39.5	8	5

<tbody>
</tbody>

Below that, I have the spot for entering time off, and that looks like this:

ii	H	I	J	K	L	M
5	Leave From	Leave To	Leave Type	Hours/Day	# of Days	Total Hrs
6	1/10/2019	1/10/2019	Sick	8	1	8
7	1/17/2019	1/21/2019	Vacation	8	3	24
8	1/24/2019	1/24/2019	Sick	5	.625	5

<tbody>
</tbody>

So in the example, I have the equations I need that figures out the work days between the "Leave Dates" in columns L:M, but I can't figure out what the formula would be in Column D to look for the dates in table ii, determine if it's "Sick" or "Vacation," and return the correct values. This covers partial sick or vacation days, which is why I have the setup like this.