A little Excel help

vstruggs

New Member
Joined
Jul 3, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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
 

Attachments

  • sample 1.PNG
    sample 1.PNG
    39.3 KB · Views: 12

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,587
Office Version
  1. 365
Platform
  1. MacOS
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
 
Last edited:

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,587
Office Version
  1. 365
Platform
  1. MacOS
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,594
Messages
5,625,688
Members
416,127
Latest member
MALEPINZON

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top