How to subtract time difference and add 24 hours if time is after midnight?

[helpneeded2]

I have a sheet that has a column for an Arrival Time (column E) and a Departure Time (column F). If the Departure time is after midnight, then it will never be later than 4:00am. The Arrival time will never be earlier than 8:00am.

I want the sheet to show the time difference.

I thought this formula might do it, but it errors out when the time is greater than midnight.

=IF(AND(HOUR(F3)>=0,HOUR(F3)<5),F3+TIME(24,0,0),F3)-E3

Can anyone please suggest a tweak I can do to make this work?

 

[Sufiyan97]

Do you want something like this?

Book3
	E	F	G
1	Arriaval	Departure	Difference
2	4:00	8:00	4:00
3	11:00 PM	8:00	9:00
4
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=F2-E2+(F2<E2)

 

[JamesCanale]

MrExcelPlayground7.xlsx
	G	H	I
20	14:00	3:15	13:15
21	8:00	16:30	8:30
Sheet1

Cell Formulas
Range	Formula
I20:I21	I20	=IF(H20<=4/24,H20+1-G20,H20-G20)

 

[Fluff]

Try it like

=IF(AND(HOUR(F3)>=0,HOUR(F3)<5),F3+1,F3)-E3

 

[helpneeded2]

Try it like

=IF(AND(HOUR(F3)>=0,HOUR(F3)<5),F3+1,F3)-E3

Thank you, that works great.

 

[Fluff]

You're welcome & thanks for the feedback.