Forum Discussion
Counting sequence dates without weekends
Hi Community,
I hope you are doing well, I am trying to count the consecutive days employees took vacation. I have a data set with the employee ID and the date they took a vacation, I need to calculate the continuous number of days that the employees took. If there is a weekend in between the days, it would still need to count them as continuous days.
I've gave it a try on excel and I was not successful I also have power bi but haven't tried it.
Do you have any suggestions for a formula in excel or power bi that could help me get this? here is a sample of my data set with the expected answer:
Thank you so much for your help,
J
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.
2 Replies
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!
