Time Differences over a day

[originalmedic]

Hi All,

I've tried to do some formulas to calculate time differences between two times. My problem comes when the times go over to the next day it doesn't calculate or says an incorrect time. Can someone advise? Attached image

I am trying to find out the difference between 06:02pm and 10:36am the next day

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1
2		18:02:00		10:36:00	16:34:00
3		06:22:00		12:33:00	06:11:00
Master

Cell Formulas
Range	Formula
E2:E3	E2	=IF(B2>D2,1+D2-B2,D2-B2)

 

[Joe4]

Let's say that your data posted above is in cells A2:D2.
Then, as long as your dates are entered as valid dates and your time is entered as valid time, you could do:

=SUM(C2:D2)-SUM(A2:B2)

 

[awoohaw]

add the date and time of each then subtract.

mr excel questions 27.xlsm
	A	B	C	D	E
1	2023-03-23	18:02	2023-03-24	10:36	16:34
originalmedic

Cell Formulas
Range	Formula
E1	E1	=(C1+D1)-(A1+B1)

 

[originalmedic]

Perfect, thank you

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[samlea22]

add the date and time of each then subtract.

mr excel questions 27.xlsm
	A	B	C	D	E
1	2023-03-23	18:02	2023-03-24	10:36	16:34
originalmedic

Cell Formulas
Range	Formula
E1	E1	=(C1+D1)-(A1+B1)

I can't seem to get this to work, if the difference I am calculating is over 2 days. See below - if i am calculating between 24/3/23 11:00 to 26/03/23 14:00 my sum is showing 03:00 instead of 26:00

does anyone know a way around this or a different formula? i am using the one shown by Awoohaw

 

[awoohaw]

you would need to format your time calculation as [hh]:ss

 

[Fluff]

That is 51 hours, not 26
Format the cell as [h]:mm:ss

 

[samlea22]

you would need to format your time calculation as [hh]:ss

Great that's worked thank you!