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
From	To	No of days off	Leave from	Leave to
Monday	Sunday

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]7/8/2019[/TD]
[TD="align: right"]7/14/2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/12/2019[/TD]
[TD="align: right"]7/24/2019[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]7/15/2019[/TD]
[TD="align: right"]7/21/2019[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/26/2019[/TD]
[TD="align: right"]8/2/2019[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]7/22/2019[/TD]
[TD="align: right"]7/28/2019[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]7/29/2019[/TD]
[TD="align: right"]8/4/2019[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

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
From	To	No of days off	Reason	Leave from	Leave to	Type
Sick
PTO
Vacation

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]7/8/2019[/TD]
[TD="align: right"]7/14/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]Sick[/TD]
[TD="align: right"]7/12/2019[/TD]
[TD="align: right"]7/24/2019[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]7/15/2019[/TD]
[TD="align: right"]7/21/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]40[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]Sick[/TD]
[TD="align: right"]7/26/2019[/TD]
[TD="align: right"]8/7/2019[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]7/22/2019[/TD]
[TD="align: right"]7/28/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]32[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]Sick, PTO[/TD]
[TD="align: right"]9/3/2019[/TD]
[TD="align: right"]9/4/2019[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]7/29/2019[/TD]
[TD="align: right"]8/4/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]40[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]PTO[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]8/5/2019[/TD]
[TD="align: right"]8/11/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]24[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]PTO[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]8/12/2019[/TD]
[TD="align: right"]8/18/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]8/19/2019[/TD]
[TD="align: right"]8/25/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8/26/2019[/TD]
[TD="align: right"]9/1/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9/2/2019[/TD]
[TD="align: right"]9/8/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]Vacation[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]9/9/2019[/TD]
[TD="align: right"]9/15/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=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}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=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,""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]i[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date From[/TD]
[TD]Date To[/TD]
[TD]Reg Hours[/TD]
[TD]Vac Hours[/TD]
[TD]Sick Hours[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/7/2019[/TD]
[TD]1/13/2019[/TD]
[TD]40[/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/14/2019[/TD]
[TD]1/20/2019[/TD]
[TD]38[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1/21/2019[/TD]
[TD]1/27/2019[/TD]
[TD]39.5[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ii[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Leave From[/TD]
[TD]Leave To[/TD]
[TD]Leave Type[/TD]
[TD]Hours/Day[/TD]
[TD]# of Days[/TD]
[TD]Total Hrs[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1/10/2019[/TD]
[TD]1/10/2019[/TD]
[TD]Sick[/TD]
[TD]8[/TD]
[TD]1[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1/17/2019[/TD]
[TD]1/21/2019[/TD]
[TD]Vacation[/TD]
[TD]8[/TD]
[TD]3[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1/24/2019[/TD]
[TD]1/24/2019[/TD]
[TD]Sick[/TD]
[TD]5[/TD]
[TD].625[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

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.