Forum Discussion
Counting sequence dates without weekends
Attached is a workbook with one possible solution.
The data is assumed to be grouped (whether or not it is sorted) by employee, with Vacation Date values for an employee in chronological order. This technique uses four formulas in four additional columns (only the last need be visible):- Calculation of the Next Weekday is just based on Saturday and Sunday being non-work days.
- If you need to allow for company holidays also, Next Scheduled Workday must be calculated; for this, a lookup is performed into the Holidays worksheet.
- Run [of consecutive workdays taken as a vacation by one employee] ID values are calculated sequentially from an arbitrary starting number. Its formulas assume the data starts in row 2 ("… IF( ROW()=2, 1, …").
- Consec[utive] Days of Vac'n is calculated as a count of rows that have the same Run ID value.
Hopefully your version of Excel will support all of the functions used here. (If your version of Excel also supports the LET function, column D calculations could be readily merged into the column E formulas.)
If you have a problem with this workbook, or have related questions, please identify which version of Excel you are using. And please consider attaching a workbook next time, so we don't have to retype all of your sample data.
Attached is a workbook with one possible solution.
The data is assumed to be grouped (whether or not it is sorted) by employee, with Vacation Date values for an employee in chronological order. This technique uses four formulas in four additional columns (only the last need be visible):
- Calculation of the Next Weekday is just based on Saturday and Sunday being non-work days.
- If you need to allow for company holidays also, Next Scheduled Workday must be calculated; for this, a lookup is performed into the Holidays worksheet.
- Run [of consecutive workdays taken as a vacation by one employee] ID values are calculated sequentially from an arbitrary starting number. Its formulas assume the data starts in row 2 ("… IF( ROW()=2, 1, …").
- Consec[utive] Days of Vac'n is calculated as a count of rows that have the same Run ID value.
Hopefully your version of Excel will support all of the functions used here. (If your version of Excel also supports the LET function, column D calculations could be readily merged into the column E formulas.)
If you have a problem with this workbook, or have related questions, please identify which version of Excel you are using. And please consider attaching a workbook next time, so we don't have to retype all of your sample data.
- Hi Snowman55, thank you so much for your help with this. It works great and factors in the holidays, this is exactly what I was searching for.
Thank you!
