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

[freeb1893]

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.

 

[bebo021999]

In G1, it contains the specified start time, and H1 contains the specified end time. The main formula is divided into 3 parts:

Part 1: Full end date - full start date
=NETWORKDAYS(A2, B2)

Part 2: Subtract hours greater than 8:00 from the result of Part 1
-(MAX(MOD(A2,1),$G$1)-$G$1)/8*24

Part 3: Subtract hours less than 16:00 from the result of Part 2
-($H$1-MIN($H$1,MOD(B2,1)))/8*24

The final formula: Part 1 - Part 2 - Part 3
=ROUND(NETWORKDAYS(A2, B2)-(MAX(MOD(A2,1),$G$1)-$G$1)/8*24-($H$1-MIN($H$1,MOD(B2,1)))/8*24,6)

Note: In practice, if the start time is earlier than 8:00 (e.g., 7:00) or the end time is later than 16:00 (e.g., 20:00), it is still considered to start at 8:00 and end at 16:00.
(In my sample it is yellow range)

Book1
	A	B	C				G	H
1	Start	End	Hour				08:00	16:00
2	28-04-23 Fri 12:00	02-05-23 Tue 16:00	2.50
3	16-05-23 Tue 08:00	16-05-23 Tue 16:00	1.00
4	16-05-23 Tue 07:00	17-05-23 Wed 18:00	2.00
5	16-05-23 Tue 08:00	18-05-23 Thu 16:00	3.00
6	16-05-23 Tue 08:00	19-05-23 Fri 16:00	4.00
7	16-05-23 Tue 08:00	20-05-23 Sat 16:00	4.00
8	16-05-23 Tue 08:00	21-05-23 Sun 16:00	4.00
9	16-05-23 Tue 08:00	22-05-23 Mon 16:00	5.00
10	08-05-23 Mon 08:30	10-05-23 Wed 08:06	1.95
11	16-05-23 Tue 08:00	24-05-23 Wed 16:00	7.00
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=ROUND(NETWORKDAYS(A2,B2)-(MAX(MOD(A2,1),$G$1)-$G$1)/8*24-($H$1-MIN($H$1,MOD(B2,1)))/8*24,6)

 

[freeb1893]

In G1, it contains the specified start time, and H1 contains the specified end time. The main formula is divided into 3 parts:

Part 1: Full end date - full start date
=NETWORKDAYS(A2, B2)

Part 2: Subtract hours greater than 8:00 from the result of Part 1
-(MAX(MOD(A2,1),$G$1)-$G$1)/8*24

Part 3: Subtract hours less than 16:00 from the result of Part 2
-($H$1-MIN($H$1,MOD(B2,1)))/8*24

The final formula: Part 1 - Part 2 - Part 3
=ROUND(NETWORKDAYS(A2, B2)-(MAX(MOD(A2,1),$G$1)-$G$1)/8*24-($H$1-MIN($H$1,MOD(B2,1)))/8*24,6)

Note: In practice, if the start time is earlier than 8:00 (e.g., 7:00) or the end time is later than 16:00 (e.g., 20:00), it is still considered to start at 8:00 and end at 16:00.
(In my sample it is yellow range)

Book1
	A	B	C				G	H
1	Start	End	Hour				08:00	16:00
2	28-04-23 Fri 12:00	02-05-23 Tue 16:00	2.50
3	16-05-23 Tue 08:00	16-05-23 Tue 16:00	1.00
4	16-05-23 Tue 07:00	17-05-23 Wed 18:00	2.00
5	16-05-23 Tue 08:00	18-05-23 Thu 16:00	3.00
6	16-05-23 Tue 08:00	19-05-23 Fri 16:00	4.00
7	16-05-23 Tue 08:00	20-05-23 Sat 16:00	4.00
8	16-05-23 Tue 08:00	21-05-23 Sun 16:00	4.00
9	16-05-23 Tue 08:00	22-05-23 Mon 16:00	5.00
10	08-05-23 Mon 08:30	10-05-23 Wed 08:06	1.95
11	16-05-23 Tue 08:00	24-05-23 Wed 16:00	7.00
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=ROUND(NETWORKDAYS(A2,B2)-(MAX(MOD(A2,1),$G$1)-$G$1)/8*24-($H$1-MIN($H$1,MOD(B2,1)))/8*24,6)

Thank you! Now if I wanted to change the start and end time range where basically I look at all 24 hours of the day as constituting 1 day rather than 8 hours, how would I adjust this formula to do that? I've played around with this, but can't seem to figure out how to tweak this to do that

 

[awoohaw]

So, you want to count hours with no "off" time so 1 day is 24 hours?
For instance, do you want the number of hours from 10 AM on day 1 to 4:00 PM on day 3? Are you concerned about weekends or holidays?
You may want to start a new thread with the specific question, as it seems this thread was a slightly different question.

 

[freeb1893]

So, you want to count hours with no "off" time so 1 day is 24 hours?
For instance, do you want the number of hours from 10 AM on day 1 to 4:00 PM on day 3? Are you concerned about weekends or holidays?
You may want to start a new thread with the specific question, as it seems this thread was a slightly different question.

Will do! And then maybe a more related question, out of curiosity how hard is it to adjust the formula to look at maybe a 10 hour day rather than an 8 hour day? I've been playing around with the formulas changing 8's to 10's, but seems like there's more too it than that to get it work with a 10 hour timeframe rather than 8 hours?

 

[awoohaw]

what are your new start and end times? Which suggested solution are you trying to alter, you haven't marked the correct solution.

 

[freeb1893]

what are your new start and end times? Which suggested solution are you trying to alter, you haven't marked the correct solution.

So these are the test start and end times I'm working with, with 8:00AM through 4:00PM being my "8 hour full work day":

Start Date / Time	End Date / Time	Total Hours / 8 = Days
10/4/2023 9:00​	10/4/2023 8:00​	0​
10/4/2023 7:00​	10/4/2023 15:00​	0.875​
10/4/2023 7:00​	10/4/2023 18:00​	1​
10/3/2023 10:00​	10/4/2023 14:00​	1.5​
10/3/2023 6:00​	10/4/2023 14:00​	1.75​
10/1/2023 0:00​	10/30/2023 12:00​	20.5​
10/4/2023 0:00​	10/4/2023 23:59​	1​
9/25/2023 14:38​	9/26/2023 13:31​	0.860417​
5/10/2023 0:00​	5/10/2023 16:00​	1​

So I've actually made modifications to your formula, as well as bebo's formula, where both yours and bebo's produce the same results. I added some additional logic to handle some additional conditions, like if my start date and end dates are blank, or if the end date/time is earlier than than my start date/time, etc. My start time of my 8 hour timeframe is in I12 and I23 respectively below, and end time is in I13 and I24

=IF(ROUND(NETWORKDAYS(A13,B13)-(MAX(MOD(A13,1),$I$12)-$I$12)/8*24-($I$13-MIN($I$13,MOD(B13,1)))/8*24,6)<0,0,ROUND(NETWORKDAYS(A13,B13)-(MAX(MOD(A13,1),$I$12)-$I$12)/8*24-($I$13-MIN($I$13,MOD(B13,1)))/8*24,6))

=(IF(NETWORKDAYS.INTL($A24,$A24,"0000011")=0,0,IF(24*(IF(AND(INT($B24)=INT($A24),$B24-INT($B24)<TIME($I$24,0,0)),$B24-INT($B24),TIME($I$24,0,0))-IF($A24-INT($A24)<TIME($I$23,0,0),TIME($I$23,0,0),$A24-INT($A24)))<0,0,24*(IF(AND(INT($B24)=INT($A24),$B24-INT($B24)<TIME($I$24,0,0)),$B24-INT($B24),TIME($I$24,0,0))-IF($A24-INT($A24)<TIME($I$23,0,0),TIME($I$23,0,0),$A24-INT($A24)))))+IF(INT($B24)-INT($A24)<=1,0,$I$23*NETWORKDAYS.INTL($A24+1,$B24-1,"0000011"))+IF(NETWORKDAYS.INTL($B24,$B24,"0000011")=0,0,IF(INT($B24)-INT($A24)=0,0,IF(24*((IF($A24-INT($B24)>TIME($I$24,0,0),TIME($I$24,0,0),$B24-INT($B24)))-TIME($I$23,0,0))<0,0,IF(24*((IF($A24-INT($B24)>TIME($I$24,0,0),TIME($I$24,0,0),$B24-INT($B24)))-TIME($I$23,0,0))>$I$23,24*((IF($B24-INT($A24)>TIME($I$24,0,0),TIME($I$24,0,0),$A24-INT($A24)))-TIME($I$23,0,0)),24*((IF($A24-INT($B24)>TIME($I$24,0,0),TIME($I$24,0,0),$B24-INT($B24)))-TIME($I$23,0,0)))))))/$I$23