Find Hours lapsed from two Date with time stamp

[shushilm]

Hi All,

I need help with finding the hours lapsed with a formula or VBA. I have 100+ rows in excel where two columns have dates with time and I need the difference in business hours. It needs to exclude Weekends and US holidays while calculating the business hours it would be great (optional). Below is the example.

A B C
[TABLE="width: 352"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Date 1[/TD]
[TD]Date 2[/TD]
[TD]Business Hours Lapsed[/TD]
[/TR]
[TR]
[TD]3/30/2018 14:03[/TD]
[TD]3/31/2018 19:29
[/TD]
[TD]Need the result in this column[/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance,
Regards,
Shushil

 

[Chameleon64]

You could use the NETWORKDAYS function to calculate the difference (in days) between the two dates, excluding weekends and holidays (you will need to make a table with the list of US holidays), then multiple by the number of business hours per day. You would then need to adjust the result to cater for the number of business hours lapsed on the start and end dates.

There's some useful info and examples of NETWORKDAYS here.

 

[shushilm]

Thank you for the response. The problem comes with finding the hours form the format I provided, its date and time together. I am not able to split also, I will work on the working days, but I need to know how to take the difference in hours from the cell where its combine with Date and time.

 

[Chameleon64]

A date/time value consists of a whole number representing the date and a decimal representing the time. So, in cell A2, 3/30/2018 14:03 is actually interpreted by Excel as the number 43189.58542.

You can therefore extract the time element with the formula: =(A2-INT(A2))*24. This will produce the result 14.05 which is the decimal equivalent of 14 hours and 3 minutes. You can then subtract this from the end of the business day (e.g. if the end of the day is 9pm then you would subtract 14.05 from 21.00 to give 6.95 business hours).