Question Re: Working Hours in Technology Q&A

Working Hours

Former Member — Thu, 11 Jun 2009 19:53:07 GMT

Hi All,

I've been using this formula to monitor the amount of hours between two dates that have elapsed excluding weekends:

(

DateDiff ("s", {campaign.date_approved}, {campaign.date_closed}) -

(DateDiff ("ww", {campaign.date_approved}, {campaign.date_closed}, crSaturday)2460*60) -

(DateDiff ("ww", {campaign.date_approved}, {campaign.date_closed}, crSunday)2460*60)

) /60/60;

But i've now been told that the hour calculations should only apply to working hours 9 to 5.

Does any one know how i would do this? I was thinking of something like subtracting 80 from the total (16 none workable hrs per day * 5 days) but not sure if this is correct?

Any ideas any1?

Many Thanks,

Dave.

Re: Working Hours

Former Member — Fri, 12 Jun 2009 00:50:49 GMT

hi,

try this business hours formula and let me know how it goes

DATETIMEVAR StDate:= start date;

DATETIMEVAR EndDate:= end date;

NUMBERVAR Weeks;

NUMBERVAR Days;

NUMBERVAR Hours;

TIMEVAR SLA_Open := TIME(9,0,0);

TIMEVAR SLA_Close := TIME(17,0,0);

NumberVar WeekendTime ;

NUMBERVAR NonWorkTime ;

IF WeekDayName(DAYOFWEEK(StDate)) = "Saturday" THEN

StDate:= DATETIMEVALUE(DATE(DATEADD('D',2,StDate)) , SLA_Open);

IF WeekDayName(DAYOFWEEK(StDate)) = "Sunday" THEN

StDate:= DATETIMEVALUE(DATE(DATEADD('D',1,StDate)) , SLA_Open);

IF TIME(StDate) > SLA_Close THEN

StDate := DATETIMEVALUE(DATE(StDate) , SLA_Close);

IF TIME(StDate) < SLA_Open THEN

StDate := DATETIMEVALUE(DATE(StDate) , SLA_Open);

IF WeekDayName(DAYOFWEEK(endDate)) = "Saturday" THEN

endDate = DATETIMEVALUE(DATE(DATEADD('D',2,endDate)) , SLA_Open);

IF WeekDayName(DAYOFWEEK(endDate)) = "Sunday" THEN

endDate = DATETIMEVALUE(DATE(DATEADD('D',1,endDate)) , SLA_Open);

IF TIME(endDate) > SLA_Close THEN

endDate := DATETIMEVALUE(DATE(endDate) , SLA_Close);

IF TIME(endDate) < SLA_Open THEN

endDate := DATETIMEVALUE(DATE(endDate) , SLA_Open);

Weeks:= (Truncate (EndDate - dayofWeek(EndDate) + 1 - (StDate - dayofWeek(StDate) + 1)) /7 ) * 5;

Days := DayOfWeek(EndDate) - DayOfWeek(StDate) + (if DayOfWeek(StDate) = 1 then -1 else 0) +

(if DayOfWeek(EndDate) = 7 then -1 else 0);

// Non Worktime on Business days

NonWorkTime := DATEDIFF("N",DATETIMEVALUE(DATE('01-01-2001'), SLA_Close),DATETIMEVALUE(DATE('01-02-2001'),SLA_Open)) * (Weeks + Days);

//a weekend in minutes is Count of saturdays and sundays * 24 hours * 60 minutes

WeekendTime := (DateDiff("ww",stDate,Enddate, crSaturday ) +DateDiff("ww",stDate,Enddate, crSunday)) * 24 * 60;

Hours:=(DATEDIFF('N', stDATE, endDate)- NonWorkTime - WeekendTime)/60 ;

Hour

Thanks,

jyothi

Re: Working Hours

Former Member — Fri, 12 Jun 2009 07:38:32 GMT

Hi Jyothi,

Thanks for your reply.

I have tried your formula but it gives me "The keyword then is missing" error here:

IF TIME(StDate) SLA_Close THEN

endDate := DATETIMEVALUE(DATE(endDate) , SLA_Close);

I'm asuming its because of a missing > or < or = before SLA_Close but unsure as to what?

Also on the last line there is this:

Hours:=(DATEDIFF('N', stDATE, endDate)- NonWorkTime - WeekendTime)/60 ;

Hour;

Should it be:

Hours:=(DATEDIFF('N', stDATE, endDate)- NonWorkTime - WeekendTime)/60 ;

Hours;

Many Thanks for your help!

Dave.

Re: Working Hours

Former Member — Sun, 14 Jun 2009 06:30:45 GMT

Good work by Jyoti, use the formula, set StDate and EndDate as your start and end dates, I would have given 50 points to Jyoti if I could.

DATETIMEVAR StDate:= currentdate;

DATETIMEVAR EndDate:= dateadd('D',8,currentdate);

NUMBERVAR Weeks;

NUMBERVAR Days;

NUMBERVAR Hours;

TIMEVAR SLA_Open := TIME(9,0,0);

TIMEVAR SLA_Close := TIME(17,0,0);

NumberVar WeekendTime ;

NUMBERVAR NonWorkTime ;

IF WeekDayName(DAYOFWEEK(StDate)) = "Saturday" THEN

StDate:= DATETIMEVALUE(DATE(DATEADD('D',2,StDate)) , SLA_Open);

IF WeekDayName(DAYOFWEEK(StDate)) = "Sunday" THEN

StDate:= DATETIMEVALUE(DATE(DATEADD('D',1,StDate)) , SLA_Open);

IF TIME(StDate) > SLA_Close THEN

StDate := DATETIMEVALUE(DATE(StDate) , SLA_Close);

IF TIME(StDate) < SLA_Open THEN

StDate := DATETIMEVALUE(DATE(StDate) , SLA_Open);

IF WeekDayName(DAYOFWEEK(endDate)) = "Saturday" THEN

endDate = DATETIMEVALUE(DATE(DATEADD('D',2,endDate)) , SLA_Open);

IF WeekDayName(DAYOFWEEK(endDate)) = "Sunday" THEN

endDate = DATETIMEVALUE(DATE(DATEADD('D',1,endDate)) , SLA_Open);

IF TIME(endDate) > SLA_Close THEN

endDate := DATETIMEVALUE(DATE(endDate) , SLA_Close);

IF TIME(endDate) < SLA_Open THEN

endDate := DATETIMEVALUE(DATE(endDate) , SLA_Open);

Weeks:= (Truncate (EndDate - dayofWeek(EndDate) + 1 - (StDate - dayofWeek(StDate) + 1)) /7 ) * 5;

Days := DayOfWeek(EndDate) - DayOfWeek(StDate) + (if DayOfWeek(StDate) = 1 then -1 else 0) +

(if DayOfWeek(EndDate) = 7 then -1 else 0);

// Non Worktime on Business days

NonWorkTime := DATEDIFF("N",DATETIMEVALUE(DATE('01-01-2001'), SLA_Close),DATETIMEVALUE(DATE('01-02-2001'),SLA_Open)) * (Weeks + Days);

//a weekend in minutes is Count of saturdays and sundays * 24 hours * 60 minutes

WeekendTime := (DateDiff("ww",stDate,Enddate, crSaturday ) +DateDiff("ww",stDate,Enddate, crSunday)) * 24 * 60;

Hours:=(DATEDIFF('N', stDATE, endDate)- NonWorkTime - WeekendTime)/60 ;

Hours

Thanks

-Azhar

Re: Working Hours

Former Member — Tue, 16 Jun 2009 12:27:01 GMT

Thanks Guys.

I will give this a try and report back.

Many Thanks,

Dave.

Re: Working Hours

Former Member — Wed, 17 Jun 2009 09:11:08 GMT

Hi Guys,

I've set:

DATETIMEVAR StDate:= currentdate;

DATETIMEVAR EndDate:= dateadd('D',8,currentdate);

DATETIMEVAR StDate:= {campaign.date_approved};

DATETIMEVAR EndDate:= {campaign.date_closed}

But this returns 0 results? Have i missed something else? Sorry to be a pain.

Thanks,

Dave.

Re: Working Hours

Former Member — Wed, 17 Jun 2009 09:22:32 GMT

Hi All,

Sorry it's actualy returning values like this?

-709.52

-2,134.17

-1,616.90

-1,616.94

Any Ideas?

Thanks Again,

Dave.

Re: Working Hours

Former Member — Wed, 17 Jun 2009 09:31:09 GMT

Ah,

Think i've spotted it:

// Non Worktime on Business days

NonWorkTime := DATEDIFF("N",DATETIMEVALUE(DATE('01-01-2001'), SLA_Close),DATETIMEVALUE(DATE('01-02-2001'),SLA_Open)) * (Weeks + Days);

Changed to UK Date Format:

// Non Worktime on Business days

NonWorkTime := DATEDIFF("N",DATETIMEVALUE(DATE('01-01-2001'), SLA_Close),DATETIMEVALUE(DATE('02-01-2001'),SLA_Open)) * (Weeks + Days);

Basically changed mm-dd-yy to dd-mm-yy and i'm now getting the right results.

Many Thanks to all that helped. Points on the way.

Cheers,

Dave.

Re: Working Hours

Former Member — Wed, 12 Feb 2014 16:31:25 GMT

I was glad to find this formula and I know it's been a few years since it was posted, but this formula does not correctly calculate elapsed time hours if the "start date/time" occur on a Friday after NonWorkTime.

Rather it just counts all the Friday time up until midnight (for example it will count the time on Fridays between 17:00 and 23:59).

I'm trying to work through how to code around this problem now and will report back if I figure out a solution.

Re: Working Hours

Former Member — Wed, 12 Feb 2014 18:53:18 GMT

Someone more expert than I could have probably solved this within one step, but here's how I solved the Friday after hours issue:

I created a new Formula called "Friday Remainder":

IF TIME({table.field for Open Date}) < Time (17,0,0) AND

(DAYOFWEEK({table.field for Open Date})) = 6 AND

DAYOFWEEK({table.field for Close Date})<>DAYOFWEEK({table.field for Open Date}) THEN

((TIME(17,0,0) - TIME({table.field for Open Date}))/60)/60

This correctly was telling me how many hours before 17:00:00 had elapsed on Friday.

I then added the result of this formula to the result of the previous Elapsed Time formula to get the total true Elapsed Time.

I spent some time trying to work into into the original formula that was posted here by others long ago, but couldn't get it to work quite right, so I settled for adding two formulas together.