Calculate time from two columns

[tezza]

Hi all,

I'm trying to calculate the amount of time Col E has start date/time & Col G has end date/time.

This formula works fine until the shift goes past midnight and then I get ###########:

=SUM(MOD(G:G,1))-SUM(MOD(E:E,1))

How do I update the code to factor in going past midnight?

Visit Report 2 Sheets.xlsm
	E		G
1	07/Jul/2024 20:00		07/Jul/2024 22:00
2	07/Jul/2024 22:00		08/Jul/2024 08:00
3	10/Jul/2024 21:00		11/Jul/2024 08:00
4	11/Jul/2024 08:00		11/Jul/2024 09:00
5	11/Jul/2024 21:00		12/Jul/2024 08:00
Go Here

Cell Formulas
Range	Formula
E1:J5	E1	=FILTER('1000 Rows'!$A:$F,'1000 Rows'!D:D='Go Here'!A1)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E:L	Expression	=AND($F1<>$F2,$F1<>"")	text	NO

 

[BradleyS]

I just used G1-E1, then formatted the cells as [h]:mm. The [] shows hours in elapsed time

 

[tezza]

I just used G1-E1, then formatted the cells as [h]:mm. The [] shows hours in elapsed time

Did you try G5-E5 then calculate the whole columns into just one cell?

 

[BradleyS]

No I created a helper column to calc the exact times of each, then summed the totals of the helper column to give me 35 hours

 

[tezza]

No I created a helper column to calc the exact times of each, then summed the totals of the helper column to give me 35 hours

I managed to solve it:

=SUM(VALUE(H:H))-SUM(VALUE(F:F))