Elapsed Time (h):mm:ss

Red Corvette

New Member
Joined
Jul 16, 2019
Messages
9
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.
 

Some videos you may like

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
Joined
Jul 2, 2014
Messages
2,707
Code:
=(EndDate-StartDate)-(DAYS(EndDate,StartDate)-NETWORKDAYS(StartDate,EndDate,HolidayList))
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
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
Joined
Jul 16, 2019
Messages
9
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
Joined
Jul 2, 2014
Messages
2,707
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
Joined
Jul 16, 2019
Messages
9
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
Joined
Jul 2, 2014
Messages
2,707
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
Joined
Jul 16, 2019
Messages
9
Being this formula is not working for some reason what would the formula be to only include work days.
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top