 # Working Hours and Days calculations

• I am trying to generate the SLA breach report for a helpdesk.The SLA is 42 hours(working). I am trying to calculate the Network Days and Network Hours between two dates with times. I want to know how many business days and hours are between the two days.

Variables:
- 'Case Open_Date' (A1)
- 'Case Close_Date.' (B1)
-'Difference Time' (E1)
-

Constants:
Work Days = Monday to Friday
Work Hrs = 09:00 AM to 11:00 PM (no lunch break)

Formats:
Date Received: dd/mm/yyyy hh:mm
Date Actioned: dd/mm/yyyy hh:mm
Response Time: d - hh:mm

Yours sincerely
Zia

## Files

• Re: Working Hours and Days calculations

Try:

=24*MAX(0,(NETWORKDAYS(A2,B2)-1)*(D2-C2)+MOD(B2,1)-MOD(A2,1))

formatted as General

or

=MAX(0,(NETWORKDAYS(A2,B2)-1)*(D2-C2)+MOD(B2,1)-MOD(A2,1))

formatted as [h]:mm

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Working Hours and Days calculations

Thanks a ton NBVC for taking out your time to help me.However, the formulae given below does not solve the issue.Let me explain more what I am trying to dy by an example:

Working days are from Monday to Friday and the Day start @09:00 am to 23:00 (11pm)

For example the first case was recd on 9/16/2013 11:04

So the number of hours it remained open on 16 sept is 23:00-11:04=11:56 hours

And it was resolved on 17 September.So the number of hours it remained open on 17 sept is 09:00-13:01=04:01

So the total resolution time is 11:56+04:01=15:57 hours

Can you give it a shot again..

Thanks,
Zia

• Re: Working Hours and Days calculations

Try my second formula again

=MAX(0,(NETWORKDAYS(A2,B2)-1)*(D2-C2)+MOD(B2,1)-MOD(A2,1))

formatted as [h]:mm

I get 15:56 for that sample (a minor rounding difference).

Where there is a will there are many ways. Finding one that works for you is the challenge!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!