Take date/time and calculate the time in between excluding weekends and including partial days (looking at full 24 hour 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?

In cells A1 and B1 I have 2/19/2024 12:00PM and 2/20/2024 6:00PM respectively.

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

 

[Jeffrey Mahoney]

You may have had you number formatting wrong

Book2
	A	B	C	D	E
1	2/19/2024 12:00 PM	2/20/2024 6:00 PM		1.25	1 day 6 hours
2	2/18/2024 8:00 AM	2/20/2024 6:00 PM		2.42	2 days 10 hours
Sheet1

Cell Formulas
Range	Formula
D1:D2	D1	=B1-A1
E1:E2	E1	=LET(Days,INT(D1),Hours,ROUND((D1-INT(D1))*24,2),Days&IF(Days>1," days "," day ")&IF(INT(Hours)=Hours,TEXT(Hours,"#"),TEXT(Hours,"#.##"))&" hours")

 

[freeb1893]

You may have had you number formatting wrong

Book2
	A	B	C	D	E
1	2/19/2024 12:00 PM	2/20/2024 6:00 PM		1.25	1 day 6 hours
2	2/18/2024 8:00 AM	2/20/2024 6:00 PM		2.42	2 days 10 hours
Sheet1

Cell Formulas
Range	Formula
D1:D2	D1	=B1-A1
E1:E2	E1	=LET(Days,INT(D1),Hours,ROUND((D1-INT(D1))*24,2),Days&IF(Days>1," days "," day ")&IF(INT(Hours)=Hours,TEXT(Hours,"#"),TEXT(Hours,"#.##"))&" hours")

Thanks! Is there a way I can also integrate excluding weekends from the results?

 

[Jeffrey Mahoney]

Book2
	A	B	C	D	E	F
1	2/16/2024 9:15 AM	2/19/2024 10:15 AM		1.04	1 day 1 hours	Weekend
2	2/14/2024 10:00 AM	2/15/2024 4:00 PM		1.25	1 day 6 hours
3	2/16/2024 9:15 AM	2/16/2024 6:15 PM		0.38	0 day 9 hours
4	2/1/2024 7:00	2/7/2024 17:00		4.42	4 days 10 hours	Weekend
Sheet1

Cell Formulas
Range	Formula
D1:D4	D1	=NETWORKDAYS(A1,B1)-2+(INT(A1)+1-A1)+(B1-INT(B1))
E1:E4	E1	=LET(Days,INT(D1),Hours,ROUND((D1-INT(D1))*24,2),Days&IF(Days>1," days "," day ")&IF(INT(Hours)=Hours,TEXT(Hours,"#"),TEXT(Hours,"#.##"))&" hours")

​

​

​

 

[freeb1893]

Book2
	A	B	C	D	E	F
1	2/16/2024 9:15 AM	2/19/2024 10:15 AM		1.04	1 day 1 hours	Weekend
2	2/14/2024 10:00 AM	2/15/2024 4:00 PM		1.25	1 day 6 hours
3	2/16/2024 9:15 AM	2/16/2024 6:15 PM		0.38	0 day 9 hours
4	2/1/2024 7:00	2/7/2024 17:00		4.42	4 days 10 hours	Weekend
Sheet1

Cell Formulas
Range	Formula
D1:D4	D1	=NETWORKDAYS(A1,B1)-2+(INT(A1)+1-A1)+(B1-INT(B1))
E1:E4	E1	=LET(Days,INT(D1),Hours,ROUND((D1-INT(D1))*24,2),Days&IF(Days>1," days "," day ")&IF(INT(Hours)=Hours,TEXT(Hours,"#"),TEXT(Hours,"#.##"))&" hours")

​

​

​

That worked! Thank you so much!