A little Excel help

[vstruggs]

In air traffic control and I am making an aircraft log with aircraft's callsigns, type aircraft, departure times, arrival times etc.
When working with times dealing with military aircraft, all times are in UTC time. NO local times ex. 9:00 P.M/21:00L more like 02:00 the next Julian date.

What I am looking for:
1. A possible a formula to make my spreadsheet utilize UTC time. What I have so far is =NOW+5/24 in R2 and =NOW() in Q2 For example, when it's 06/30/2020 2359z the new Zulu date will 07/01/2020 0001z

2. A conditional formatting that will turn the cell FONT RED when the aircraft is 30 minutes past the ETA (estimated time of arrival) cell or possibly the whole line B2-M2 and B3 -M:3 change red. Also, when the ATA (actual time of arrival) when that aircraft arrives I would like to have the font return normal but in BLACK font signifying that flight is done/closed out.
The formulas I've used to get the ETA's for aircraft's in M2 is =SUM(K2:L2) and in M3 is =SUM(K3:L3).
End results for the spreadsheet is to have a complete log in font in black after all aircraft's arrivals.

All the help will be appreciated.

Victor

 

[etaf]

question 1
=MOD(NOW()+(5/24),1)

I assume its ATA is blank , until an aircraft arrives
so

when the ATA (actual time of arrival) when that aircraft arrives I would like to have the font return normal but in BLACK font signifying that flight is done/closed out.

Conditional format
use a formula
=$ Cell with ATA in <> ""
then format font black, with no fill colour
the $ fixes the cell - so you can select a row or range and all the rang eor row will colour

 

[etaf]

A conditional formatting that will turn the cell FONT RED when the aircraft is 30 minutes past the ETA (estimated time of arrival) cell or possibly the whole line B2-M2 and B3 -M:3 change red

use
=ETA Cell +TIMEVALUE("0:30:0")
that gives us the time expected
then you could use NOW() > ETA Cell +TIMEVALUE("0:30:0")
as a formula and set font red in conditional formatting
again a $ will fix the cell
NOW() > $ ETA Cell +TIMEVALUE("0:30:0")

do you have the cells
Not sure i understand the reason for 2 rows
M2 and M3