How do I define work days
For one of my formulas, I need to count our specific work days between 2 dates. Our work days are only: Sunday Monday Tuesday Wednesday Please help!
Forum Discussion
It's been a while - but what you can do is:
Count the number of days between the start and end dates ToDays(startdate - enddate)
Divide that number by 7.
Multiply by 4 to get the work days in whole weeks
Then you need to adjust for the number of days that remain that are work days for example;
if there are 10 days in the period, and the first day of the period is Monday.
Mo Tu We Th Fr Sa Su Mo Tu We
Then you have 4 work days + Mo Tu We all of which are work days
So 7 total work days
Basically the adjustment is calculated as follows:
If the first date Mon
- If the number of remaining days is less than 7, add 3
- If the number of remaining days is less than 3, and all of them
...
That's the basic formula.
------------------------------
Neil Schneider
------------------------------
Count the number of days between the start and end dates ToDays(startdate - enddate)
Divide that number by 7.
Multiply by 4 to get the work days in whole weeks
Then you need to adjust for the number of days that remain that are work days for example;
if there are 10 days in the period, and the first day of the period is Monday.
Mo Tu We Th Fr Sa Su Mo Tu We
Then you have 4 work days + Mo Tu We all of which are work days
So 7 total work days
Basically the adjustment is calculated as follows:
If the first date Mon
- If the number of remaining days is less than 7, add 3
- If the number of remaining days is less than 3, and all of them
...
That's the basic formula.
------------------------------
Neil Schneider
------------------------------
This should give you a good starting point:
------------------------------
Paul Peterson
------------------------------
If(DayOfWeek(endDate) > 3, 0, ToDays(NextDayOfWeek(startDate, 3) - startDate)) + (Int(ToDays(If(ToDays(endDate - NextDayOfWeek(endDate, 0)) = 7, endDate, NextDayOfWeek(endDate, 0)) - If(ToDays(NextDayOfWeek(startDate, 6) - startDate) = 7, startDate, NextDayOfWeek(startDate, 6))) / 7) * 4 )+ If(DayOfWeek(endDate) > 3, 0, ToDays(endDate - PrevDayOfWeek(endDate, 0)))
------------------------------
Paul Peterson
------------------------------
- Sorry, just noticed an error.
Try This instead:
If(DayOfWeek(startDate) > 3, 0, ToDays(NextDayOfWeek(startDate, 3) - startDate)) +
(Int(ToDays(If(ToDays(endDate - NextDayOfWeek(endDate, 0)) = 7, endDate, NextDayOfWeek(endDate, 0)) - If(ToDays(NextDayOfWeek(startDate, 6) - startDate) = 7, startDate, NextDayOfWeek(startDate, 6))) / 7) * 4 )+ If(DayOfWeek(endDate) > 3, 0, ToDays(endDate - PrevDayOfWeek(endDate, 0)))
------------------------------
Paul Peterson
------------------------------