Number of working hours & mins between two date/time fields

[Chats]

I need to calculate the number of working hours between two date time fields (StartTime and EndTime). The calculation should be based on a working day that starts at 08:30 in the morning, and ends at 17:30 at night, monday to friday.

I may also need to take account of holiday dates.

I have tried searching the web etc and can find formulas that do this for working days, but not working hours. I am using Crystal 8.5

Can anybody help?

Thanks

Ade

 

[Naith]

Hey Ade,

Ken Hamady is your man for this.

Look at these URLs:

http://www.kenhamady.com/form13.html

(Business Hours)

http://www.kenhamady.com/form01.html

(Business Days)

All the best,

Naith

 

[Chats]

Naith,

Thanks, this didn't seem to give me exactly what I needed, but got me off to an excellent start.

I am calculating SLA's, I have a start time and an end time for an event. I need to calculate how many working hours there are between these two dates. The clock stops at 5pm Friday and starts again 8am monday untiul 5pm monday etc. Work can still be carried out outside these times, but does not count as working hours.

I'll paste what I have done so far in case it is of interest to anybody, though I am still testing that it all works OK and it could do with a bit of tidying up yet.

It is pretty much Ken's formula until the convert to hours comment.

WhilePrintingRecords;
DateVar Start := date({DATABASE.FIELD}); //Replace this field with your Starting Date field
DateVar End := date({DATABASE.FIELD}); //Replace this field with your Ending Date field
DateTimeVar StartDT:= {DATABASE.FIELD}; //Replace this field with your Ending Date field
DateTimeVar EndDT:={DATABASE.FIELD}; //Replace this field with your Ending Date field
NumberVar Weeks;
NumberVar Days;
NumberVar Hol:= 0;
NumberVar TotalDays:=0 ;
NumberVar TotalHours:=0;
TimeVar StartTime:=Timevalue(08,0,0);
TimeVar EndTime:=Timevalue(17,0,0);
NumberVar WorkingDay:=(EndTime-StartTime)/3600;

NumberVar StartDayOfWeek:=DayOfWeek(Start);
NumberVar EndDayOfWeek:=DayOfWeek(End);

NumberVar ElapsedStart:=0;
NumberVar ElapsedEnd:=0;

NumberVar HoursWorked:=0;

//Figure the number of Calendar "Rows" involved and count 5 days for each:
Weeks:= (Truncate (End - dayofWeek(End) + 1 - (Start - dayofWeek(Start) + 1)) /7 ) * 5;

//Adjust the first and last weeks based on when in the week you start and end
Days := DayOfWeek(End) - DayOfWeek(Start) + 1
+ (if DayOfWeek(Start) = 1 then -1 else 0) //adjust for starting on a Sunday:
+ (if DayOfWeek(End) = 7 then -1 else 0); //adjust for ending on a Saturday:

//Adjust for Holidays in the period between the start and end dates:
//if Date(1999,01,01) in start to end then Hol:= Hol+1 else Hol:= Hol;
//if Date(1999,01,18) in start to end then Hol:= Hol+1 else Hol:= Hol;
//if Date(1999,02,16) in start to end then Hol:= Hol+1 else Hol:= Hol;
//if Date(1999,05,31) in start to end then Hol:= Hol+1 else Hol:= Hol;

//Assemble the adjusted work days

TotalDays:=Weeks + Days - Hol ;

// convert to hours

TotalHours:=TotalDays*WorkingDay;

//Caculate the number of minutes into the 1st working day that elapse before Start

If not(StartDayOfWeek in [1,7]) and
TimeValue (StartDT)>StartTime
Then ElapsedStart:=TimeValue(StartDT)-StartTime;

ElapsedStart:=ElapsedStart/3600;

//Calculate the number of minutes before the end of the last working day which elapse after Finish

if not(EndDayOfWeek in [1,7]) and
TimeValue (EndDT)<EndTime
Then ElapsedEnd:=EndTime-TimeValue(EndDT);

ElapsedEnd:=ElapsedEnd/3600;

// Subtract Elapsed Time from TotalHours

HoursWorked:=TotalHours-ElapsedStart-ElapsedEnd;

// Set HoursWorked to zero if all completed outside working hours

if HoursWorked<0 then HoursWorked:=0;

// Print Result

HoursWorked;