Calculate only working hours between two dates excluding weekends

[Spoorthi]

How should I calculate working hours between two dates? Say if start at 9/25/2009 7:26:13 PM and finish at 10/20/2009 9:46:13 AM, the function should return 245:20:00 because the working hours are from 8am to 11 PM (8 - 23), and there are weekends between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates. And also how can I exclude holidays if there are any.

 

[SubZeroie]

You should be able to use a version of the formula I suggested in post #11 - try this

=(NETWORKDAYS(D2,E2)-1)*("17:00"-"8:30")+IF(NETWORKDAYS(E2,E2),MEDIAN(MOD(E2,1),"17:00","8:30"),"17:00")-MEDIAN(NETWORKDAYS(D2,D2)*MOD(D2,1),"17:00","8:30")

custom format result cell as [h]:mm

This has helped me so much, Firstly Thank You.

But i do have a small problem i can not resolve.

I have been asked to calculate the numbers of hours to include Monday to Friday but also Saturday from 08:00 to 12:30pm

Can you please advise if there is a way to do this? I know this formula does not calculate Saturdays, but we could add the additional times in on the Sunday, would this work? ?

Any help is very much appreciated, i have been working on this for some time now and i am just hitting a brick wall now.

 

[James006]

Hi,

Since the function networkdays() does exclude Saturdays ans Sundays, there are only two possibilities :

1. If you want to keep the networkdays function, you do need to add you own UDF for Saturdays

or

2. If you do not want to keep networkdays, you do need to rebuild the whole lot with the workday() function

Hope this clairifies

Cheers

 

[James006]

Hi,

For the sake of helping out future readers ...
http://www.mrexcel.com/forum/excel-questions/346901-workday-formulas-time-format-work-hours.html

 

[Jayakumar]

Hi,

I'm trying to calculate total hours taken to complete a ticket which has Opened and Resolved data & time as below,

Resolved 19-06-2015 12:09:15
Opened 19-06-2015 11:36:41

Business Hours - 8AM to 5PM CST
Weekends are off

The above formula throws an error when I tried to calculate the Hours taken to complete the ticket excluding Non-Business hours and weekends. Could you help?

 

[barry houdini]

Hello Jayakumar, welcome to MrExcel, which formula did you try?

If your resolved time/date is in A2 and opened time/date is in B2 then the following formula will suffice, assuming open and resolved time/date will always be within working hours

=(NETWORKDAYS(B2,A2)-1)*("17:00"-"8:00")+MOD(A2,1)-MOD(B2,1)

 

[Jayakumar]

Hi Barry....The open and resolved date and time would be round the clock but want the formula to calculate the only working hours. For eg, if the ticket has been opened at 6AM CST and it was resolved by 10AM CST, the time taken to resolve the ticket should be 2 hours only NOT 4 hours, as the working hour is from 8AM till 5PM CST. Please let me know your thoughts.

 

[Jayakumar]

Hi Barry....The open and resolved date and time would be round the clock but want the formula to calculate the only working hours. For eg, if the ticket has been opened at 6AM CST and it was resolved by 10AM CST, the time taken to resolve the ticket should be 2 hours only NOT 4 hours, as the working hour is from 8AM till 5PM CST. Please let me know your thoughts.

Could someone help me with the above query?

Thanks!

 

[barry houdini]

Ok, that would be this version......

=(NETWORKDAYS(B2,A2)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),"17:00","8:00")

 

[rajaryan]

Need formula to calculate business hours and if the request is raised and addressed between nonworking hours then formula just simply be closed time minus open time and highlight the cell in a colour using conditional formatting

 

[unclebigpete]

Hullo everybody
I have a similar but different problem - I need to calculate elapsed times within the period 8am Monday and 8am Saturday, but extending over several weeks (and not counting the hours outside that range). For example from 12noon on Friday 7th August until 10am on Wednesday 26th August is 190 hours, calculated manually. What is the formula for that, please?
Thanks
Peter