SUMIF time between formula

[jmw116]

Hello all,

Not an excel person what so ever. I use excel for my pilot log book logging, the problem comes for night time logging.

My works software spits out in a CSV format file for me, and has departure date, take off time and landing time all in separate collums. What I need is something along the lines of
If cell A later than 2000 (8PM) than log difference between A+B until the cut off time 0700 (7AM). Or something along these lines. Please see below what it looks like outputted from the software. Obviously there is some slight error involved with the time change because both times are in local and not UTC but what would be a correct formula to caculate a total based on between times between the blocks?

Date	A/C Type	Tail	Origin	Dest	Depart	Arrive	total
10/31/2021	CRJ	N*****	SHV	DEN	7:16	8:36	2.33

Option B would be to convert the local time to UTC time. Is there a way to make excel do that?

 

[Toadstool]

Hi Jmw116,

To convert times to UTC then you'd need a database of local time/UTC for each airport along with the dates they change for DST, if applicable. I'm sure there's proprietary software available to calculate this (and even account for local civil sunset and sunrise times) but I don' think Excel can do that for you.

Below I show a flight duration calculation. You only have departure date so there's a small wrinkle as the calculation needs to add a day to departure if arrival time is less than departure time (i.e. you landed the next day).

For the calculation of times between 8pm and 7am it gets trickier as it needs to figure out the date for the 8pm and for the 7am (e.g. if depart is 2am and arrive is 9am then the 8pm you want is the previous day).

Please note you'll probably need to specifically format result cells as Time, and if you include totals then you will need to format as Custom [hh:mm] as normal Time formats don't handle values over 24 hours.

JMW116.xlsx
	A					F	G	H	I	J
1	Date					Depart	Arrive	Duration		Duration between 8pm and 7am
2	31-Oct-21					7:16	8:36	1:20		0:00
3	27-Nov-21					19:27	21:57	2:30		1:57
4	30-Nov-21					19:38	22:08	2:30		2:08
5	03-Dec-21					7:49	22:19	14:30		2:19
6	04-Dec-21					20:00	22:30	2:30		2:30
7	07-Dec-21					20:11	2:00	5:49		5:49
8	10-Dec-21					12:00	14:30	2:30		0:00
9	13-Dec-21					0:11	2:41	2:30		2:30
10	15-Dec-21					12:22	21:00	8:38		1:00
11	16-Dec-21					7:15	19:30	12:15		0:00
12	17-Dec-21					18:00	6:30	12:30		10:30
13
14							Totals	67:32		28:43
1st

Cell Formulas
Range	Formula
H2:H12	H2	=IF(G2<F2,(A2+1+G2)-(A2+F2),(A2+G2)-(A2+F2))
J2:J12	J2	=MAX(MIN(((IF(G2<F2,(A2+1+G2),(A2+G2)))),(IF(OR(G2<F2,F2>TIME(7,0,0)),A2+1+TIME(7,0,0),$A2+TIME(7,0,0))))-MAX((A2+F2),(IF(F2<TIME(7,0,0),A2-1+TIME(20,0,0),A2+TIME(20,0,0)))),0)
H14,J14	H14	=SUM(H2:H13)