Working hours calc between dates

[simongoodman]

I would like to calculate working hours between two dates  based on a  created order datetimestamp and a completion datetimestamp.

I have looked at the various discussions and have an idea how to calculate a single fixed start work time and end work time for all working days.

There are two main issues.

1-Any out of hours created orders (before the start or after end of the working day) are assigned to the start of the next working day. Eg if an order is created Monday 20 pm the amended datetime is Tuesday 8pm. If the completion date&time is Tuesday 2pm the working hour difference is 6 hours.

2-Next how could I calculate working hours where say Monday to Friday working day starts 8am and finishes 18pm and Saturday starts 9pm and ends 14pm.

Thank you for any assistance

Simon

[sunny_talwar]

Have you checked this link out? Business Working Hours Calculation

[simongoodman]

Hi Sunny

I believe this link discussion looks to exclude Saturday and uses the NetWorkDays which is Monday to Friday. Also there is a single work start and end time.

[MK_QSL]

Hi Simon,

Provide some sample data..  I would work on it during weekends.

I have created similar application on IT ticketing system for my company.

Kindly provide sample data along with expected output.

[MK_QSL]

Or you can use this... similar approach...

Re: Calculate hours between two Date/Time strings

[swuehl · Accepted solution]

Have a look at my first reply to the thread that Manish linked to.

You can create a solution without using the Networkdays() function and add different start and end times with some added logic, something along these lines:

Set TimestampFormat = 'M/D/YY hh:mm TT';

Set vHol = '41130,41140';

INPUT:
LOAD *, recno() as ID INLINE [
DT1, DT2
8/9/12 11:08 AM,8/9/12 2:57 PM
8/18/12 09:20 AM,8/20/12 01:13 PM
8/17/12 09:20 AM,8/20/12 01:13 PM
8/17/12 09:20 AM,8/19/12 01:13 PM
6/27/12 12:41 PM,    7/6/12 4:38 PM
6/29/12 4:45 PM,    7/6/12 4:19 PM 
8/1/12 09:00 AM, 8/3/12 10:00 AM
8/3/12 03:00 PM, 8/6/12 09:00 AM
6/24/16 05:00 PM, 6/27/16 09:00 AM
6/24/16 07:00 PM, 6/27/16 09:00 AM
];

TMP:
LOAD ID, Date,
  if(IterNo=1, rangemin(rangemax(frac(DT1),If(Weekday(Date)<5,maketime(8),MakeTime(9))),If(WeekDay(Date)<5,maketime(18),Maketime(14))), If(Weekday(Date)<5,maketime(8),Maketime(9))) as Start,
  if(daystart(DT1)+IterNo-1=daystart(DT2), rangemax(If(Weekday(Date)<5,maketime(8),maketime(9)),rangemin(frac(DT2),If(Weekday(Date)<5,maketime(18),Maketime(14)))),If(Weekday(Date)<5,Maketime(18),Maketime(14))) as End;
LOAD  ID,
daystart(DT1)+iterno()-1 as Date,
DT1,DT2,iterno() as IterNo
Resident INPUT
while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)
LOAD
ID,
interval(sum(End-Start)) as Duration
Resident TMP  where WeekDay(Date)<6 and not match(Date,$(vHol))  group by ID;

drop table TMP;

[simongoodman]

Thank you all for your assistance. I believe Swuehl's approach will work, I testing it on live data.

[vinayr67]

Thanks for this solution.

Is it possible to provide some commentary on your script as I need to change some parameters (working times, etc.)?

Thanks.