Duration formula (only business hours)

[ronin316]

Any help would be appreciated

I have the following formula:

( if( [start_time] = null, [duration] , if( [end_time] = null , [duration] , if( [end_time] > [start_time] , ([end_time] - [start_time] + 30 ) /60 , if( [start_time] > [end_time] , ( ( (Time( 23 , 59 , 59) - [Start_time] ) + 31) / 60 + (Hour( [end_time]) X 60 ) + Minute( [End_time] , 0 ) ) ) ) ) + ( ( ( [end_date] - [start_date] ) X 24 ) X 60 )

This determines the number of minutes between the fields. I now need to minus the non-working minutes that may fall in the range. This is a form formula and I am not able to create queries inside this application. The working hours are 9-5 Monday to Friday.

Any help would be appreciated, I am having a brain burp

Thanks

 

[Madawc]

That doesn't look like Crystal. You should be able to do something like

if isnull(start_time) or isnull(end_time) then 0
else
DateDiff("n", start_time, end_time)

That ought to give you the number of minutes - though what about lunch? You should be able to subtract this from 9 to 5, 8*60=480 minutes, if that's what you are after. And assuming it can't be more than 480.

Madawc Williams (East Anglia, UK)