Difference between times over midnight

[ISlo]

Hi all - I'm sure I've done this before but can't find the solution which I think lies simply in the formatting. I'm trying to calculate time elapsed which includes values that cross over midnight, but I just can resolve. It just won't return a value - can any help? Format below is [hh]:mm

Thanks

Start	End	Elapsed
22:30​	23:30​	01:00​
23:30​	00:10​	########​

 

[Georgiboy]

Welcome to the forum,

You can use date & time and do the same calculation, if the option to use date as well is not available then maybe the below:

Book1
	A	B	C
1	Start	End	Elapsed
2	22:30	23:30	01:00
3	23:30	00:10	00:40
Sheet1

Cell Formulas
Range	Formula
C2:C3	C2	=MOD(B2-A2,1)

 

[Joe4]

Note that the issue is the all valid time entries also have a date component. If you do not explicitly enter the date piece, it defaults to today.
So in your second entry, you are subtracting the 11/17/2023 11:30 PM from 11/17/2023 12:10 AM, which results in a negative value.

You could use a formula like this, that would add one day if the column A entry is larger than the column B entry:

=IF(B2>A2,B2-A2,(B2+1)-A2)

or you can use the simpler formula provided by Georgiboy, which gets to the same place by dividing the result by one and taking the remainder.

 

[ISlo]

Welcome to the forum,

You can use date & time and do the same calculation, if the option to use date as well is not available then maybe the below:

Book1
	A	B	C
1	Start	End	Elapsed
2	22:30	23:30	01:00
3	23:30	00:10	00:40
Sheet1

Cell Formulas
Range	Formula
C2:C3	C2	=MOD(B2-A2,1)

That's great thank you!!