Calculate the number of Weekdays between two dates

Resolving The Problem

The report specificatioin attached and the description outlined below is used to calculate the amount of weekdays between two dates.

To accomplish this you have to go about this in a number of steps.

First you get the amount of days between the two dates, and add one to that so that you can count the starting day.

(_days_between([RETURNDATE],[ORDERDATE])+1)

Then you calculate the amount of weekend days. To do this you have to add the Days Between to the Day of the Week, and then FLOOR that value to get the highest integer result.

You then divide that number by 7 to get the amount of WEEKS and then multiply that by 2 to calculate the amount of WeekEnd days.

floor((_days_between([RETURNDATE],[ORDERDATE])+_day_of_week([ORDERDATE],1))/7)*2

Name this calculation "Weekend days".

In the floor calculation you ignore half weekends. So you have to accommodate for this. If the start date started on a Sunday, then you had one too many weekend days. And if that end date ended on a Saturday we would also have one too many weekend days.

So to get around this you have to add the following IF statement to the calculation.

floor((_days_between([RETURNDATE],[ORDERDATE])+_day_of_week([ORDERDATE],1))/7)*2 
- if (_day_of_week([ORDERDATE],1)=7) then (1) else (0) 
+ if (_day_of_week([RETURNDATE],1)=6) then (1) else (0)

This will remove a weekend day if the start day is on a Sunday, and it will add a weekend day if the ending date is on a Saturday.

Doing this gives you the correct amount of weekend days.

You then subtract the Days Between (the calculation from the first step) by the Weekend Days (calculation from the second and third step).

(_days_between([RETURNDATE],[ORDERDATE])+1) - [Weekend days]

See the attached report spec to see the exact syntax used. This report specification connects to the GO Sales (query) package.

Also note, that this example might need adjustments depending on the database vendor, and their handling of date and time datatypes. Ie. on Oracle fiels should be casted to date.