Crystal Reports Date formulas 1

[jchewsmith]

Is there a way to create a formula to calculate the number of hours that it takes for something to happen but exclude weekends?

Example:

Ship Date - Order Date * 24 (hours) = hours to ship

I want to calculate this but exclude weekends?

 

[pmax9999]

The most efficient way to achieve this is to join your existing tables to a Calendar Table that lists all dates so as to exclude weekends from the report (and if you also wish to exclude holidays this too can be achieved if the calendar table has holidays flagged). Assuming your database does not include one you could create one, either in the database itself or in MS Excel.

A simple (but inefficient) alternative is to use a formula like this:

Local NumberVar T := {Table.End_Date}-{Table.Start_Date};
Local NumberVar D;
Local NumberVar i;

For i := 1 to T do
If      Not(DayOfWeek({Table.Start_Date} + i) in [1,7])
Then    D := D + 1
Else    D;

D * 24

This code assumes the dates are actual dates not date/times. If date/times, Use DateDiff to calculate the initial T value.

Hope This helps.

Cheers
Pete