# Employee Time Off Tracking (among other items)

#### rallysport096

##### New Member
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!

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### Aryatect

##### Active Member
Hi, If I understood correctly below should work:

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

#### rallysport096

##### New Member
Hi, If I understood correctly below should work:

ABCDEF
1FromToNo of days offLeave fromLeave to
27/8/20197/14/201917/12/20197/24/2019
37/15/20197/21/201957/26/20198/2/2019
47/22/20197/28/20194
57/29/20198/4/20195
6MondaySunday

</tbody>
Sheet1

Array Formulas
CellFormula
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))}

</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

##### Active Member
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!

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

#### rallysport096

##### New Member
ABCDHIJ
1FromToNo of days offReasonLeave fromLeave toType
27/8/20197/14/20198Sick7/12/20197/24/2019Sick
37/15/20197/21/201940Sick7/26/20198/7/2019PTO
47/22/20197/28/201932Sick, PTO9/3/20199/4/2019Vacation
57/29/20198/4/201940PTO
68/5/20198/11/201924PTO
78/12/20198/18/20190
88/19/20198/25/20190
98/26/20199/1/20190
109/2/20199/8/201916Vacation
119/9/20199/15/20190

</tbody>
Sheet1

Array Formulas
CellFormula
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,""))}

</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

##### New Member
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.

Replies
0
Views
177
Replies
14
Views
150
Replies
2
Views
73
Replies
0
Views
117
Replies
1
Views
548

1,130,042
Messages
5,639,731
Members
417,108
Latest member
Thein Than

### 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.

### Which adblocker are you using?

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

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