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]

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

 

[Red Corvette]

Thank You for the quick response but for what ever reason the formula did not work the result comes up #VALUE !

 

[SpillerBD]

Use F9 to calculate each formula segment to determine where the error is occurring.
Verify the Cell and Range references. The array for the Holiday List should not include the list header.

 

[Red Corvette]

Don't know anything about using F9 here is how I entered the formula =(P2-N2)-(DAYS(P2,N2)-NETWORKDAYS(N2,P2,Info!C36:C43))

 

[SpillerBD]

Verify each part in a separate cell.
All references should be serial date values or numbers. Check using ISNUMBER.

My guess would be a value in C36:C43 is not a serial date or recognizable to Excel as Date Text.

 

[Red Corvette]

I managed to figure out how to use ISNUMBER via YouTube and the end date comes up False how do I fix it?

 

[SpillerBD]

You will probably need to retype. If it was text matching one of the formats for your Region, Excel would probably convert it in the background.
You can try using the Text-To-Columns feature and just make sure the format matches the original.

 

[Red Corvette]

I copied the start date and past special formats and it now shows True but I still have the same formula result #VALUE !

 

[Red Corvette]

Being this formula is not working for some reason what would the formula be to only include work days.