Calculate hours between 2 given dates with time bound range

[Davesh Garg]

Hi All,


Please help me in extracting hours from two responses given date and time, keep in mind to calculate if response received within business hours.

For Ex:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Response1[/TD]
[TD]Response2[/TD]
[TD]Difference (Hours)[/TD]
[/TR]
[TR]
[TD]09/07/2018 10:00:00 AM[/TD]
[TD]09/07/2018 02:00:00 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09/07/2018 10:00:00 AM[/TD]
[TD]09/11/2018 07:00:00 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09/07/2018 05:00:00 AM[/TD]
[TD]09/13/2018 05:00:00 AM[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

* Here, please consider business time starts and ends at 08:00 AM - 05:00 AM
* Saturday and Sunday should be excluded
* Any Holiday as per already having a table should be excluded

Hours calculation should be processed as per business time considered.

Please let me know if anything is not clear. Appreciate your help and response on the same.Thanks

DG

 

[Davesh Garg]

Hi All,

Any help would be highly appreciated.

Thanks,
Dave

 

[ppittman]

=(B3-A3)*24

This seems like it would work. Where "response1" is located in column 'A'

 

[Davesh Garg]

Hi ppittman,

Thanks for your response. However, this will consider the time where replies received in out of business hours also. So hours cannot be beyond 9 hours (8:00AM-05:00PM) at the max for a day. And final hours would not considered any holiday given in another table in my excel. It should also exclude Sat and Sun.

I require one formula which consider all these criteria's also.

Appreciate if you could provide an update on this. Thanks

D ---

 

[Davesh Garg]

Hi ppittman,


Thanks for your response. However, this will consider the time where replies received in out of business hours also which could lead to more than 9 hours.


So overall 3 criteria's to be met for my final hours calculation--
a) hours cannot be beyond 9 hours (8:00AM-05:00PM) at the max for a day. For eg,
--- if response1 received is before 8:00AM, then start time should be calculated from 8:00AM till we send a response.
--- if response2 sent after 5:00PM on any day, then end time should be considered as 5:00PM for that day.
--- if response1 received on 10:00AM and response2 sent on next day by 3:00PM, then total final hours should be 14hours because it is falling under business hours.
b) It would not considered any holiday given in another table in my excel.
c) It should also exclude Sat and Sun, so we can consider Network days formula here.

But overall I am not able to find formula to have this final output. I require one formula which consider all these criteria's also. Please let me know if you require any additional information. Appreciate if you or anyone can provide an update on this. Thanks


D ---: