Between two dates and times during work hours ONLY,

[Mohan Kumar]

Hi All,


This is my first post and I'm sure not the last.


I need HELP!


I have to construct an aging report weekly that will track the time duration between two dates and times during work hours ONLY, excluding weekends and holidays.


I can not find a formula that will give me what I need to complete this task.


Below is the format of the start and end dates for the tickets:


Start Date

6/10/2011 1:19:35 PM


End Date

11/10/2011 1:10:13 PM


Between these two date I have to find out how long it took for the ticket to close during business hours and not including weekends.


Work Week - Monday - Friday

Work Hours - 8AM - 6PM

Holiday - depended upon what holidays are in the month


The result should be in the hours format.


Regards,

Mohan

 

[Faseeh]

Hi Mohan Kumar,


Welcome to the forums!!


Why you are mentioning time when the working hrs are between 8 AM : 6 PM ?? Kindly see this file:


http://dl.dropbox.com/u/60644346/Mohan%20Kumar.xlsx


Regards,

Faseeh

 

[Luke M]

Looks like Chandoo tackled this very question here:

http://chandoo.org/wp/2010/09/10/working-hours-formula/