Number of Weekdays in Time Period 1

[RobReynolds]

Is there a way to determine the number of weekdays between 2 dates?
Ex. Start Date End Date Weekdays
5-01-04 5-15-04 10
5-16-04 5-31-04 11
6-01-04 6-15-04 11

 

[DoubleD]

This rather lengthy calculation is provided on the Cognos Knowledgebase. I have NOT tested or validated this in any way. I am only providing it as published on their website.

Problem Description

Is it possible to determine the number of business days between two dates?

Solution Description

The following assumptions will be made:

1. The end date must be greater than the start date else then -99.
2. The calculation will not function correctly if either the start or end date is a weekend.

This can be resolved by filtering the start and end date to be a weekday as follows:

mod (date-to-days-from-1900 (datetime-to-date (Order Dt)), 7) in (1,2,3,4,5)
and mod (date-to-days-from-1900 (datetime-to-date (Closed Dt)), 7) in (1,2,3,4,5)


First condition: the dates are in successive weeks


if (integer-divide (date-to-days-from-1900(datetime-to-date(Closed Dt)), 7) =
(integer-divide (date-to-days-from-1900(datetime-to-date(Order Dt)), 7) + 1)) then

(mod (date-to-days-from-1900 (datetime-to-date (Closed Dt)), 7) -
mod (date-to-days-from-1900 (datetime-to-date (Order Dt)), 7) + 5)

Note: Subtract the two day numbers and add the single week offset


Second condition: end date is previous to start date


else if (integer-divide (date-to-days-from-1900(datetime-to-date(Closed Dt)), 7) <
integer-divide (date-to-days-from-1900(datetime-to-date(Order Dt)), 7)) then

(-99)


Third condition: The dates are in the same week

else if (integer-divide (date-to-days-from-1900(datetime-to-date(Closed Dt)), 7) =
integer-divide (date-to-days-from-1900(datetime-to-date(Order Dt)), 7)) then

(mod (date-to-days-from-1900(datetime-to-date(Closed Dt)), 7) -
mod (date-to-days-from-1900(datetime-to-date(Order Dt)), 7))


Final Condition: There are some number of weeks between the dates

else

(((integer-divide (date-to-days-from-1900(datetime-to-date(Closed Dt)), 7) -
integer-divide (date-to-days-from-1900(datetime-to-date(Order Dt)), 7) - 1) * 5)
+ (mod (date-to-days-from-1900(datetime-to-date(Closed Dt)), 7) + 5) -
mod (date-to-days-from-1900(datetime-to-date(Order Dt)), 7))

Pain is stress leaving the body.

DoubleD

 

[kdemulder]

And this as a cry from the help department:
Cognos, PLEASE put this as a standard calculation in next release...

 

[drlex]

...especially as this is available in Crystal Reports (which also allows for Public Holidays)

soi la, soi carre