Elapsed Time (h):mm:ss

[Red Corvette]

Hello, new to the forum with little knowledge of Excel. I've been searching for a formula for elapsed time showing in (h):mm:ss between to date/time Stamps i.e. Start date/time 7/10/2019 10:53 AM and finish date/time 7/10/2019 2:20 PM that will exclude weekends & holidays to no avail. I have the holidays listed on a separate tab that will need to manually adjusted each year. Any help greatly appreciated.

 

[SpillerBD]

The formula I provided was copied from a tested environment.
It calculate the total elapsed time in the first part. Then subtracts the number of days not available. The number of days not available is the total number of days less the number of workdays as provided by the Excel function NETWORKDAYS.

 

[Red Corvette]

I re-formatted the columns and this does seem to work other than it is adding 24 hours to the results?

 

[Red Corvette]

I used a deferent formula that seems to be working i.e. +NETWORKDAYS(stat,end,Holdays)+start-end-INT(end-start)-1 The only issue with this is the elapsed time columns full of #### what would need to be added for the elapsed time column to be blank until there is a date/time in the end column?

 

[Red Corvette]

Sorry beginning of formula is =NETWORKDAYS not +

 

[SpillerBD]

Sorry beginning of formula is =NETWORKDAYS not +

That's a LOTUS thing.

"########" as a result formatted as a date/time may indicate the resultant value is Negative. Try inversing with a -1.

 

[SpillerBD]

=(EndDate-StartDate)-(DAYS(EndDate,StartDate)-NETWORKDAYS(StartDate,EndDate,HolidayList))

NETWORKDAYS will provide a result of 1 for the same day.
Minor modofication.

=(EndDate-StartDate)-(DAYS(EndDate,StartDate)-NETWORKDAYS(StartDate,EndDate,HolidayList)-1)