Calculate Time and Date between 2 Date

[earthworm]

I need to calculate date and time as under
Suppose

Start date and time = 3 May 2024 8:45 PM

End Date and time = 7 May 2024 3:PM

Saturday Sunday excluded

How time time it took between start date and end date in total duration in Hours and Days

 

[Jeffrey Mahoney]

Will this work?

Book2
	A	B	C
3	Fri	5/3/2024 8:45
4			3 days 23 hours 1 minutes
5	Tue	5/7/2024 15:00
Sheet1

Cell Formulas
Range	Formula
A3,A5	A3	=B3
C4	C4	=NETWORKDAYS(B3,B5)+TIME(24,0,0)&" days "&TEXT((B3-INT(B3))+(B5-INT(B5)),"hh")&" hours "&TEXT((B3-INT(B3))+(B5-INT(B5)),"m")&" minutes"

 

[Jeffrey Mahoney]

I think it returned 3 days because the beginning time was in the morning and the end time is in the afternoon. Subtracting one day may solve the issue

Book2
	A	B	C
3	Fri	5/3/2024 8:45
4			2 days 23 hours 1 minutes
5	Tue	5/7/2024 15:00
Sheet1

Cell Formulas
Range	Formula
A3,A5	A3	=B3
C4	C4	=NETWORKDAYS(B3,B5)+TIME(24,0,0)-1&" days "&TEXT((B3-INT(B3))+(B5-INT(B5)),"hh")&" hours "&TEXT((B3-INT(B3))+(B5-INT(B5)),"m")&" minutes"

 

[Jeffrey Mahoney]

I again jumped the gun and thought I had solved it quickly until I took a harder look

Book2
	A	B	C
3	Fri	5/3/2024 8:45
4			2 days 6 hours 15 minutes
5	Tue	5/7/2024 15:00
Sheet1

Cell Formulas
Range	Formula
A3,A5	A3	=B3
C4	C4	=NETWORKDAYS(B3,B5)-1&" days "&HOUR(MOD(1-(B3-INT(B3))+(B5-INT(B5)),1))&" hours "&MINUTE(MOD(1-(B3-INT(B3))+(B5-INT(B5)),1))&" minutes"

 

[earthworm]

I again jumped the gun and thought I had solved it quickly until I took a harder look

Book2
	A	B	C
3	Fri	5/3/2024 8:45
4			2 days 6 hours 15 minutes
5	Tue	5/7/2024 15:00
Sheet1

Cell Formulas
Range	Formula
A3,A5	A3	=B3
C4	C4	=NETWORKDAYS(B3,B5)-1&" days "&HOUR(MOD(1-(B3-INT(B3))+(B5-INT(B5)),1))&" hours "&MINUTE(MOD(1-(B3-INT(B3))+(B5-INT(B5)),1))&" minutes"

I haven't tried it can you please convert the answer in whole number for easy reference

 

[Jeffrey Mahoney]

You should try it. You could remove this portion: &MINUTE(MOD(1-(B3-INT(B3))+(B5-INT(B5)),1))&" minutes"

 

[alansidman]

If you are willing to work with Power Query, then look at this link for an alternative means

Excel Forum

Count working days between two dates in Power Query

www.myonlinetraininghub.com

 

[Cubist]

I think this should work if your dates don't start or end on weekends.

Book1
	A	B	C	D
1	Start	End
2	5/3/May 8:45	5/7/May 15:00	54:15	<- Total Time (h:m). Formatted as [h]:mm
3			2	<- Days. Formatted as General
4			6:15	<- hours:minutes. Formatted as [h]:mm
Sheet6

Cell Formulas
Range	Formula
C2	C2	=NETWORKDAYS(A2,B2)-1+MOD(B2,1)-MOD(A2,1)
C3	C3	=INT(C2)
C4	C4	=MOD(C2,1)

 

[Dave Patton]

another option

T202405.xlsm
	A	B	C
1	Start	End
2	Fri 3-May-24 8:45	Tue 7-May-24 15:00	54:15
4f

Cell Formulas
Range	Formula
C2	C2	=(B2-A2-NETWORKDAYS.INTL(A2,B2,"1111100"))