Take date/time and calculate the time in between excluding weekends and including partial days

F

freeb1893

Board Regular
Joined
Jul 30, 2012
Messages
234
Office Version
  1. 365
Platform
  1. Windows
I have 2 dates with dates and time, where I'd like to subtract them and determine the amount of time between those dates. I'd like to exclude weekends in the result. I'd also like to consider only partial days if the time for either the 1st date or the 2nd date is within the day sometime.

Anyone know how to put this in a formula?

Also if possible, I'd like to be able to define what my full day is, based on a start time and end time, and factor that into consideration.

For example if in cells G1 and H1 for references, I define the start of a date as 8:00AM and end of that same date as 4:00PM respectively as what constitutes 1 full day in my result.

In cells A1 and B1 I have 4/28/2023 12:00PM and 5/2/2023 4:00PM respectively.

I'd like the result of my formula referencing those cells to be 2.50 days.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Try this:

Book2
ABCDEF
11st Day HoursIntervening Day(s) HoursLast Day HoursTotal Hours / 8 = Days
2Fri 2023-04-28 12:00Tue 2023-05-02 16:004.008.008.002.50
3
4
5Fri 2023-04-28 12:00Sat 2023-04-29 16:00Sun 2023-04-30 16:00Mon 2023-05-01 16:00Tue 2023-05-02 16:00
64.008.008.008.008.00
7
Sheet1
Cell Formulas
RangeFormula
C2C2=24*(TIME(16,0,0)-IF(A2-INT(A2)<TIME(8,0,0),TIME(8,0,0),A2-INT(A2)))
D2D2=8*NETWORKDAYS.INTL(A2+1,B2-1,"0000011")
E2E2=24*((IF(A2-INT(B2)>TIME(16,0,0),TIME(16,0,0),B2-INT(B2)))-TIME(8,0,0))
F2F2=(24*(TIME(16,0,0)-IF(A2-INT(A2)<TIME(8,0,0),TIME(8,0,0),A2-INT(A2))) +8*NETWORKDAYS.INTL(A2+1,B2-1,"0000011") +24*((IF(A2-INT(B2)>TIME(16,0,0),TIME(16,0,0),B2-INT(B2)))-TIME(8,0,0))) /8
 
Upvote 0
awoohaw said:
Try this:

Book2
ABCDEF
11st Day HoursIntervening Day(s) HoursLast Day HoursTotal Hours / 8 = Days
2Fri 2023-04-28 12:00Tue 2023-05-02 16:004.008.008.002.50
3
4
5Fri 2023-04-28 12:00Sat 2023-04-29 16:00Sun 2023-04-30 16:00Mon 2023-05-01 16:00Tue 2023-05-02 16:00
64.008.008.008.008.00
7
Sheet1
Cell Formulas
RangeFormula
C2C2=24*(TIME(16,0,0)-IF(A2-INT(A2)<TIME(8,0,0),TIME(8,0,0),A2-INT(A2)))
D2D2=8*NETWORKDAYS.INTL(A2+1,B2-1,"0000011")
E2E2=24*((IF(A2-INT(B2)>TIME(16,0,0),TIME(16,0,0),B2-INT(B2)))-TIME(8,0,0))
F2F2=(24*(TIME(16,0,0)-IF(A2-INT(A2)<TIME(8,0,0),TIME(8,0,0),A2-INT(A2))) +8*NETWORKDAYS.INTL(A2+1,B2-1,"0000011") +24*((IF(A2-INT(B2)>TIME(16,0,0),TIME(16,0,0),B2-INT(B2)))-TIME(8,0,0))) /8
Click to expand...
Seems something is still a bit off. For example, if determining the time between 5/8/23 8:30 AM and 5/10/23 9:06 AM, this produces the result as 2.08 days when it should be 2.03 hours?
Also if I'm looking 5/16/23 8:00 AM and 5/16/23 4:00PM, I should end up with 1.00 days, but the result is -1.00?
 
Last edited:
Upvote 0
How does this work?:

Mr excel questions 47.xlsm
ABCDEF
11st Day HoursIntervening Day(s) HoursLast Day HoursTotal Hours / 8 = Days
2Fri 2023-04-28 12:00Tue 2023-05-02 16:004.008.008.002.50
3Tue 2023-05-16 08:00Tue 2023-05-16 16:008.000.000.001.00
4Tue 2023-05-16 08:00Wed 2023-05-17 16:008.000.008.002.00
5Tue 2023-05-16 08:00Thu 2023-05-18 16:008.008.008.003.00
6Tue 2023-05-16 08:00Fri 2023-05-19 16:008.0016.008.004.00
7Tue 2023-05-16 08:00Sat 2023-05-20 16:008.0024.008.005.00
8Tue 2023-05-16 08:00Sun 2023-05-21 16:008.0024.008.005.00
9Tue 2023-05-16 08:00Mon 2023-05-22 16:008.0024.008.005.00
10Tue 2023-05-16 08:00Tue 2023-05-23 16:008.0032.008.006.00
11Tue 2023-05-16 08:00Wed 2023-05-24 16:008.0040.008.007.00
freeb1893
Cell Formulas
RangeFormula
C2:C11C2=24*(TIME(16,0,0)-IF(A2-INT(A2)<TIME(8,0,0),TIME(8,0,0),A2-INT(A2)))
D2:D11D2= IF(INT(B2)-INT(A2)<=1,0, 8*NETWORKDAYS.INTL(A2+1,B2-1,"0000011"))
E2:E11E2= IF(INT(B2)-INT(A2)=0,0, 24*((IF(A2-INT(B2)>TIME(16,0,0),TIME(16,0,0),B2-INT(B2)))-TIME(8,0,0)))
F2:F11F2=( 24*(TIME(16,0,0)-IF(A2-INT(A2)<TIME(8,0,0),TIME(8,0,0),A2-INT(A2))) + IF(INT(B2)-INT(A2)<=1,0, 8*NETWORKDAYS.INTL(A2+1,B2-1,"0000011")) + IF(INT(B2)-INT(A2)=0,0, 24*((IF(A2-INT(B2)>TIME(16,0,0),TIME(16,0,0),B2-INT(B2)))-TIME(8,0,0))) ) /8
 
Upvote 0
awoohaw said:
How does this work?:

Mr excel questions 47.xlsm
ABCDEF
11st Day HoursIntervening Day(s) HoursLast Day HoursTotal Hours / 8 = Days
2Fri 2023-04-28 12:00Tue 2023-05-02 16:004.008.008.002.50
3Tue 2023-05-16 08:00Tue 2023-05-16 16:008.000.000.001.00
4Tue 2023-05-16 08:00Wed 2023-05-17 16:008.000.008.002.00
5Tue 2023-05-16 08:00Thu 2023-05-18 16:008.008.008.003.00
6Tue 2023-05-16 08:00Fri 2023-05-19 16:008.0016.008.004.00
7Tue 2023-05-16 08:00Sat 2023-05-20 16:008.0024.008.005.00
8Tue 2023-05-16 08:00Sun 2023-05-21 16:008.0024.008.005.00
9Tue 2023-05-16 08:00Mon 2023-05-22 16:008.0024.008.005.00
10Tue 2023-05-16 08:00Tue 2023-05-23 16:008.0032.008.006.00
11Tue 2023-05-16 08:00Wed 2023-05-24 16:008.0040.008.007.00
freeb1893
Cell Formulas
RangeFormula
C2:C11C2=24*(TIME(16,0,0)-IF(A2-INT(A2)<TIME(8,0,0),TIME(8,0,0),A2-INT(A2)))
D2:D11D2= IF(INT(B2)-INT(A2)<=1,0, 8*NETWORKDAYS.INTL(A2+1,B2-1,"0000011"))
E2:E11E2= IF(INT(B2)-INT(A2)=0,0, 24*((IF(A2-INT(B2)>TIME(16,0,0),TIME(16,0,0),B2-INT(B2)))-TIME(8,0,0)))
F2:F11F2=( 24*(TIME(16,0,0)-IF(A2-INT(A2)<TIME(8,0,0),TIME(8,0,0),A2-INT(A2))) + IF(INT(B2)-INT(A2)<=1,0, 8*NETWORKDAYS.INTL(A2+1,B2-1,"0000011")) + IF(INT(B2)-INT(A2)=0,0, 24*((IF(A2-INT(B2)>TIME(16,0,0),TIME(16,0,0),B2-INT(B2)))-TIME(8,0,0))) ) /8
Click to expand...
Getting closer, but still a little off unfortunately. Some more examples if I have start date 5/8/2023 8:30:00 AM and end date 5/10/2023 8:06:00 PM, should be less than 3 days but the result is 3.45 days.
Another example start date 5/16/2023 8:00:00 AM and end date 5/16/2023 3:30:00 PM, it should be less than 1 day but the result is 1.00 days
 
Upvote 0
I'm curious if you have tried to debug the formulas I've done above? As you say I'm getting a little closer. I'm not paid to offer solutions, I do it because I like challenges. I hope you do to. Why don't you try to figure out what I've done wrong. I've started you down a path that you may be able to figure out.
 
Upvote 0
awoohaw said:
I'm curious if you have tried to debug the formulas I've done above? As you say I'm getting a little closer. I'm not paid to offer solutions, I do it because I like challenges. I hope you do to. Why don't you try to figure out what I've done wrong. I've started you down a path that you may be able to figure out.
Click to expand...
I'm working on debugging this. Could you help explain what you're doing with this formula for 1st Day Hours? I'm having trouble following what you're doing here with this:

=24*(TIME(16,0,0)-IF(A2-INT(A2)<TIME(8,0,0),TIME(8,0,0),A2-INT(A2))
 
Upvote 0
awoohaw said:
I'm curious if you have tried to debug the formulas I've done above? As you say I'm getting a little closer. I'm not paid to offer solutions, I do it because I like challenges. I hope you do to. Why don't you try to figure out what I've done wrong. I've started you down a path that you may be able to figure out.
Click to expand...
I've got the rest of this overall solution shored up, but the 1st Day Hours does not calculate correctly if the start date and end date are within the same day, it returns 8 hours for 1st Day Hours even when it's just a partial day for the 1st Day. I'm trying to tweak the 1st Day Hours formula to not result in 8 hours when the start and end are the same day and less than 8 hours
 
Upvote 0
as I wrote this up for you i was thinking I may have an error in there. I just read your last note, and I'll work on that, but still post this. Maybe this can hep you:
Mr excel questions 47.xlsm
ABCDEF
254=24*( IF(AND(INT(B2)=INT(A2),B2-INT(B2)<TIME(16,0,0)),B2-INT(B2),TIME(16,0,0))- IF(A2-INT(A2)<TIME(8,0,0), TIME(8,0,0), A2-INT(A2)))
26=24*(IF( AND(INT(B2)=INT(A2),B2-INT(B2)<TIME(16,0,0)), B2-INT(B2), TIME(16,0,0)) - IF( A2-INT(A2)<TIME(8,0,0), TIME(8,0,0), A2-INT(A2)))The first IF condition is looking for two things: 1-It is looking for if the dates in A & B are the same day AND 2- that the Time in B2 is earlier than 4 PM. If it is less than 4 PM then it subtracts the integer portion of the date/time in B. If it is equal or after 4 PM then it uses 4 PM.The second if condition looks to see if the start time in A2 is earlier than 8 AM. If it is earlier than 8 AM then it uses 8 AM, if not it uses what ever the start time is.Subtracting the two times will get the number of hours worked on the first day expressed a a decimal of 1 day. So this is multiplied by 24 to get the number of hours.
freeb1893
Cell Formulas
RangeFormula
A25A25=24*( IF(AND(INT(B2)=INT(A2),B2-INT(B2)<TIME(16,0,0)),B2-INT(B2),TIME(16,0,0))- IF(A2-INT(A2)<TIME(8,0,0), TIME(8,0,0), A2-INT(A2)))
B25B25=FORMULATEXT(A25)
 
Upvote 0
awoohaw said:
as I wrote this up for you i was thinking I may have an error in there. I just read your last note, and I'll work on that, but still post this. Maybe this can hep you:
Mr excel questions 47.xlsm
ABCDEF
254=24*( IF(AND(INT(B2)=INT(A2),B2-INT(B2)<TIME(16,0,0)),B2-INT(B2),TIME(16,0,0))- IF(A2-INT(A2)<TIME(8,0,0), TIME(8,0,0), A2-INT(A2)))
26=24*(IF( AND(INT(B2)=INT(A2),B2-INT(B2)<TIME(16,0,0)), B2-INT(B2), TIME(16,0,0)) - IF( A2-INT(A2)<TIME(8,0,0), TIME(8,0,0), A2-INT(A2)))The first IF condition is looking for two things: 1-It is looking for if the dates in A & B are the same day AND 2- that the Time in B2 is earlier than 4 PM. If it is less than 4 PM then it subtracts the integer portion of the date/time in B. If it is equal or after 4 PM then it uses 4 PM.The second if condition looks to see if the start time in A2 is earlier than 8 AM. If it is earlier than 8 AM then it uses 8 AM, if not it uses what ever the start time is.Subtracting the two times will get the number of hours worked on the first day expressed a a decimal of 1 day. So this is multiplied by 24 to get the number of hours.
freeb1893
Cell Formulas
RangeFormula
A25A25=24*( IF(AND(INT(B2)=INT(A2),B2-INT(B2)<TIME(16,0,0)),B2-INT(B2),TIME(16,0,0))- IF(A2-INT(A2)<TIME(8,0,0), TIME(8,0,0), A2-INT(A2)))
B25B25=FORMULATEXT(A25)
Click to expand...
That worked! Thank you much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Take date/time and calculate the time in between excluding weekends and including partial days (looking at full 24 hour days)
Replies
4
Views
621
freeb1893
F
C
  • Question Question
Calculating consecutive days excluding weekends and holidays
Replies
4
Views
84
croud
C
F
  • Locked
  • Question Question
Formula to count days between 2 dates/times excluding weekends but including only partial days depending on when the date/time falls (see description)
Replies
2
Views
806
Peter_SSs
Peter_SSs
W
  • Question Question
Calculate total time taken to complete task in working days (6am to 6pm)
Replies
4
Views
458
Wibab
W
B
  • Solved
Issue with finding time difference taking account of weekends and workdays.
2
Replies
15
Views
401
Joe4
Joe4

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

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