Employee Time Off Tracking (among other items)

rallysport096

New Member
Joined
Apr 22, 2017
Messages
17
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
Joined
Jun 17, 2019
Messages
308
Hi, If I understood correctly below should work:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">From</td><td style=";">To</td><td style=";">No of days off</td><td style="text-align: right;;"></td><td style=";">Leave from</td><td style=";">Leave to</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">7/8/2019</td><td style="text-align: right;;">7/14/2019</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">7/12/2019</td><td style="text-align: right;;">7/24/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">7/15/2019</td><td style="text-align: right;;">7/21/2019</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">7/26/2019</td><td style="text-align: right;;">8/2/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">7/22/2019</td><td style="text-align: right;;">7/28/2019</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">7/29/2019</td><td style="text-align: right;;">8/4/2019</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Monday</td><td style=";">Sunday</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">{=SUM(<font color="Blue">(<font color="Red">IF(<font color="Green">WORKDAY.INTL(<font color="Purple">B2,-1,1</font>)<$F$2:$F$3,WORKDAY.INTL(<font color="Purple">B2:B2,-1,1</font>),$F$2:$F$3</font>)-IF(<font color="Green">A2>$E$2:$E$3,A2:A2,$E$2:$E$3</font>)+1</font>)*(<font color="Red">B2>=$E$2:$E$3</font>)*(<font color="Red">A2<=$F$2:$F$3</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

rallysport096

New Member
Joined
Apr 22, 2017
Messages
17
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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))}

<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

Active Member
Joined
Jun 17, 2019
Messages
308
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></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">From</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">To</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">No of days off</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Reason</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Leave from</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Leave to</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Type</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7/8/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7/14/2019</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FA7D00;background-color: #F2F2F2;;">8</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FA7D00;background-color: #F2F2F2;;">Sick</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7/12/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7/24/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Sick</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7/15/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7/21/2019</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FA7D00;background-color: #F2F2F2;;">40</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FA7D00;background-color: #F2F2F2;;">Sick</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7/26/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/7/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">PTO</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7/22/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7/28/2019</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FA7D00;background-color: #F2F2F2;;">32</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FA7D00;background-color: #F2F2F2;;">Sick, PTO</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">9/3/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">9/4/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Vacation</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7/29/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/4/2019</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FA7D00;background-color: #F2F2F2;;">40</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FA7D00;background-color: #F2F2F2;;">PTO</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/5/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/11/2019</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FA7D00;background-color: #F2F2F2;;">24</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FA7D00;background-color: #F2F2F2;;">PTO</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/12/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/18/2019</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FA7D00;background-color: #F2F2F2;;">0</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FA7D00;background-color: #F2F2F2;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/19/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/25/2019</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FA7D00;background-color: #F2F2F2;;">0</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FA7D00;background-color: #F2F2F2;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/26/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">9/1/2019</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FA7D00;background-color: #F2F2F2;;">0</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FA7D00;background-color: #F2F2F2;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">9/2/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">9/8/2019</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FA7D00;background-color: #F2F2F2;;">16</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FA7D00;background-color: #F2F2F2;;">Vacation</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">9/9/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">9/15/2019</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FA7D00;background-color: #F2F2F2;;">0</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FA7D00;background-color: #F2F2F2;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">{=SUM(<font color="Blue">(<font color="Red">IF(<font color="Green">WORKDAY.INTL(<font color="Purple">B2,-1,1</font>)<$I$2:$I$10,WORKDAY.INTL(<font color="Purple">B2,-1,1</font>),$I$2:$I$10</font>)-IF(<font color="Green">A2>$H$2:$H$10,A2,$H$2:$H$10</font>)+1</font>)*(<font color="Red">B2>=$H$2:$H$10</font>)*(<font color="Red">A2<=$I$2:$I$10</font>)</font>)*8}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">{=TEXTJOIN(<font color="Blue">", ",1,IF(<font color="Red">(<font color="Green">B2>=$H$2:$H$10</font>)*(<font color="Green">A2<=$I$2:$I$10</font>)*(<font color="Green">ROW(<font color="Purple">$J$2:$J$10</font>)-ROW(<font color="Purple">$J$2</font>)+1</font>),$J$2:$J$10,""</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

rallysport096

New Member
Joined
Apr 22, 2017
Messages
17
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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,""))}

<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

New Member
Joined
Apr 22, 2017
Messages
17
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:

iABCDEF
1Date FromDate ToReg HoursVac HoursSick Hours
21/7/20191/13/2019408
31/14/20191/20/20193816
41/21/20191/27/201939.585

<tbody>
</tbody>


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

iiHIJKLM
5Leave FromLeave ToLeave TypeHours/Day# of DaysTotal Hrs
61/10/20191/10/2019Sick818
71/17/20191/21/2019Vacation8324
81/24/20191/24/2019Sick5.6255

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

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top