Elapsed day, hours, minutes minus weekends

C

chanman

New Member
Joined
Oct 14, 2008
Messages
24
Office Version
  1. 365
Platform
  1. Windows
I'm wanting to calculate the Elapsed between the start and end date/time. I can achieve this in column C. But as soon as I attempt to excludes weekends in column D the results isnt correct. What am I doing wrong??

Help very much appreciated. Thank

example.xlsx
ABCD
1StartEndDifference Difference excluding weekends
221/12/2023 06:5221/12/2023 13:300 Days 6 Hour 38 Minutes1 Days 6 Hour 38 Minutes
301/12/2023 10:2720/12/2023 15:4119 Days 5 Hour 14 Minutes-14 Days 5 Hour 14 Minutes
419/12/2023 07:2122/12/2023 10:223 Days 3 Hour 1 Minutes-4 Days 3 Hour 1 Minutes
513/12/2023 11:0828/12/2023 06:1914 Days 19 Hour 11 Minutes-12 Days 19 Hour 11 Minutes
Sheet1
Cell Formulas
RangeFormula
C2:C5C2=INT(B2-A2)&" Days "&HOUR(MOD(B2-A2,1))&" Hour "&MINUTE(MOD(B2-A2,1))&" Minutes"
D2:D5D2=NETWORKDAYS(B2, A2,2)&" Days "&HOUR(MOD(B2-A2,1))&" Hour "&MINUTE(MOD(B2-A2,1))&" Minutes"
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
example.xlsx
ABCD
1StartEndDifference Difference excluding weekends
221/12/2023 06:5221/12/2023 13:300 Days 6 Hour 38 Minutes1 Days 6 Hour 38 Minutes
301/12/2023 10:2720/12/2023 15:4119 Days 5 Hour 14 Minutes-14 Days 5 Hour 14 Minutes
419/12/2023 07:2122/12/2023 10:223 Days 3 Hour 1 Minutes-4 Days 3 Hour 1 Minutes
513/12/2023 11:0828/12/2023 06:1914 Days 19 Hour 11 Minutes-12 Days 19 Hour 11 Minutes
Sheet1
Cell Formulas
RangeFormula
C2:C5C2=INT(B2-A2)&" Days "&HOUR(MOD(B2-A2,1))&" Hour "&MINUTE(MOD(B2-A2,1))&" Minutes"
D2:D5D2=NETWORKDAYS(B2, A2,2)&" Days "&HOUR(MOD(B2-A2,1))&" Hour "&MINUTE(MOD(B2-A2,1))&" Minutes"
 
Upvote 0
How about:
=MIN(INT(B2)-INT(A2),NETWORKDAYS(A2,B2))&" Days " &TEXT(MOD(MOD(B2,1)-MOD(A2,1),1),"h ""Hours"" m ""Minutes""")
 
Upvote 0
@Scott Huish thank you very much! That worked perfectly!
Another ask.... what I if i need that as a decimal value (in column G)?

example.xlsx
ABCDEFG
1StartEndDifference Difference Days in decimalDifference excluding weekendsScotts formula :)Difference Days in decimal (excluding weekends)
221/12/2023 06:5221/12/2023 13:300 Days 6 Hour 38 Minutes0.31 Days 6 Hour 38 Minutes0 Days 6 Hours 38 Minutes?
320/12/2023 10:2720/12/2023 15:410 Days 5 Hour 14 Minutes0.21 Days 5 Hour 14 Minutes0 Days 5 Hours 14 Minutes?
419/12/2023 07:2122/12/2023 10:223 Days 3 Hour 1 Minutes3.1-4 Days 3 Hour 1 Minutes3 Days 3 Hours 1 Minutes?
513/12/2023 11:0828/12/2023 06:1914 Days 19 Hour 11 Minutes14.8-12 Days 19 Hour 11 Minutes12 Days 19 Hours 11 Minutes?
Sheet1
Cell Formulas
RangeFormula
D2:D5D2=B2-A2
E2:E5E2=NETWORKDAYS(B2, A2,2)&" Days "&HOUR(MOD(B2-A2,1))&" Hour "&MINUTE(MOD(B2-A2,1))&" Minutes"
F2:F5F2=MIN(INT(B2)-INT(A2),NETWORKDAYS(A2,B2))&" Days " &TEXT(MOD(MOD(B2,1)-MOD(A2,1),1),"h ""Hours"" m ""Minutes""")
 
Upvote 0
=MIN(INT(B2)-INT(A2),NETWORKDAYS(A2,B2))+MOD(MOD(B2,1)-MOD(A2,1),1)

Format as a number with the decimal places of your choice.
 
Upvote 0
Hey @Scott Huish - thanks again, now that I'm comparing the decimel values i have spotted something thats not quite right.

I have a new example in row 7, that spans across 2 weekends. however i'm only seeing 3 days being taken off? should be 4

example.xlsx
ABCDEFG
1StartEndDifference Difference Days in decimalDifference excluding weekendsScotts formula :)Difference Days in decimal (excluding weekends)
221/12/2023 06:5221/12/2023 13:300 Days 6 Hour 38 Minutes0.31 Days 6 Hour 38 Minutes0 Days 6 Hours 38 Minutes0.276388889
320/12/2023 10:2720/12/2023 15:410 Days 5 Hour 14 Minutes0.21 Days 5 Hour 14 Minutes0 Days 5 Hours 14 Minutes0.218055556
419/12/2023 07:2122/12/2023 10:223 Days 3 Hour 1 Minutes3.1-4 Days 3 Hour 1 Minutes3 Days 3 Hours 1 Minutes3.125694444
513/12/2023 11:0828/12/2023 06:1914 Days 19 Hour 11 Minutes14.8-12 Days 19 Hour 11 Minutes12 Days 19 Hours 11 Minutes12.79930556
618/12/2023 14:1422/12/2023 10:213.8-5 Days 20 Hour 7 Minutes4 Days 20 Hours 7 Minutes4.838194444
701/12/2023 10:1211/12/2023 10:2610.0-7 Days 0 Hour 14 Minutes7 Days 0 Hours 14 Minutes7.009722222
Sheet1
Cell Formulas
RangeFormula
D2:D7D2=B2-A2
E2:E7E2=NETWORKDAYS(B2, A2,2)&" Days "&HOUR(MOD(B2-A2,1))&" Hour "&MINUTE(MOD(B2-A2,1))&" Minutes"
F2:F7F2=MIN(INT(B2)-INT(A2),NETWORKDAYS(A2,B2))&" Days " &TEXT(MOD(MOD(B2,1)-MOD(A2,1),1),"h ""Hours"" m ""Minutes""")
G2:G7G2=MIN(INT(B2)-INT(A2),NETWORKDAYS(A2,B2))+MOD(MOD(B2,1)-MOD(A2,1),1)
 
Upvote 0
Not sure I see the problem, if you made row 7 the same time in both cells, you would see that it's 7 days, and if you look at a calendar, it's 7 days. (4 weekend days removed, Dec. 2 & 3, Dec. 9 & 10)
Dec. 1, Dec. 4, Dec. 5, Dec. 6, Dec. 7, Dec. 8, Dec. 11

The problem is you're comparing it to subtracting dates which is not inclusive of both dates, so if if you subtract Jan 2 from Jan 1 you get 1 to include both dates you would have to add 1, so for your comparison to be correct your formula in D2 should be =B2-A2+(B2-A2>=1) so that it will ad 1 for date differences that are 1 or more days apart
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D
A
  • Question Question
Need to convert days, hours, minutes and second text into hh:mm:ss
Replies
7
Views
947
Aadya
A
S
  • Question Question
Using IFS to calculate a task due by date but needing to remove weekends
Replies
3
Views
474
awoohaw
awoohaw
A
  • Solved
Macro getting stuck on average when no data is present
Replies
5
Views
429
amphead
A
S
  • Solved
Formula for displaying day, hours and minutes from a different cell locations if the cell does not calculate the first formula
Replies
3
Views
576
etaf
etaf

Forum statistics

Threads
1,224,813
Messages
6,181,107
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top