# Elapsed Time (h):mm:ss

#### Red Corvette

##### New Member
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.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### SpillerBD

##### Well-known Member
Code:
``=(EndDate-StartDate)-(DAYS(EndDate,StartDate)-NETWORKDAYS(StartDate,EndDate,HolidayList))``

#### Red Corvette

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

#### SpillerBD

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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

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

#### SpillerBD

##### Well-known Member
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

##### New Member
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

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

1,101,949
Messages
5,483,840
Members
407,415
Latest member
Anton1999

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...