Need help with a time function

[AtlantaCargo]

OK this may be a bit confusing....i'm creating a spreadsheet for "Winter Weather Operations" at the ATL airport. We will need to track the flight from:

1) the gate [A]
2) when it enters a certain area (called a "pad")
3) when it leaves the pad [C]
4) When it takes off [D]

It looks like this:

Airline Gate (A) Enter Pad (B) Exit Pad (C) Airborne (D)
DELTA123 00:00 00:15 00:30 00:45

* We need to know when a certain aircraft is taking longer than "90 mins" from A--B, C--D, etc.

*****Here's my dilema******​

* I have functions set up to tell me how long each travel time is (i.e. from A--B, B--C, Total time of travel, etc.

*I can only tell if an aircraft is X mins late if i input the time manually.​

****Is there a function I can use to insert a timer? or to prompt me when an aircraft is running behind? Like i said, I'll only know after the fact.​

If you can help me out, you're flight this season through ATL will be very smooth!!!​

Wes McDonald, Airside Operations Agent
wesley.mcdonald@atlanta-airport.com​

 

[GlennUK]

*I can only tell if an aircraft is X mins late if i input the time manually.

Input the time manually where? What cell? To get the time now, use the NOW() function, which updates every time recalculation occurs ... to force recalculation regularly you might need a macro to use an OnTime method to call another macro that justs does recalculation.

 

[Emma Schoolderman]

The first solution that comes to mind (if I am understanding your needs correctly) would be something like the below:

<TABLE style="WIDTH: 362pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=482 border=0 x:str><COLGROUP><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 512" width=14><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" width=136><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 11pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=14 height=17></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>A</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 60pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=80>B</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=79>C</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 82pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=109>D</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 102pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=136>E</TD></TR><TR style="HEIGHT: 24.75pt; mso-height-source: userset" height=33><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 11pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 24.75pt; BACKGROUND-COLOR: transparent" align=right width=14 height=33 x:num>1</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 60pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=80>Normal travel time</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=79>Actual Time taken</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 82pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=109>Latest Estimate on total time</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 102pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=136></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>2</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">a-b</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>30</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: yellow" align=right x:num>50</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=IF(ISBLANK(D3),C3,D3)">50</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>3</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">b-c</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>30</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: yellow" align=right x:num>20</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=IF(ISBLANK(D4),C4,D4)">20</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>4 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">c-d</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>30</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: yellow"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=IF(ISBLANK(D5),C5,D5)">30</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>5 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Total Time</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=SUM(C3:C5)">90</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=SUM(E3:E5)">100</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:fmla='=IF(E6-C6>0,"Expected Late",IF(E6=C6,"On Time",IF(E6<C6,"Early","Late")))'>Expected Late</TD></TR></TBODY></TABLE>

Whereby Column B is the normal time it should take. Column C is where you input the time it has taken so far and column D contains the formula
=IF(ISBLANK(c2),b2,c2) (Taken from cell E2 - just copy the formula down for rows 3 and 4 )

Then cell D5 =sum(D2:D4)

Cell E5 is the formula =IF(D5-B5>0,"Expected Late",IF(D5-b5<0,"Expected Early","On-time")<B5,"EXPECTED p Time?)).<B5,?EARLY?,?LATE?)))< Early?,?On>)
<B5,"EXPECTED p mins ?&B5-D5&? time?)) Early?,?on time?))< time?))<B5,?EXPECTED <B5,?EXPECTED>

 

[Emma Schoolderman]

Sorry had troubles trying to post the full reply
The last formula you can also improve to show how late or how early it is expected to be if you modify the formula in E5 to be:
=IF(D5-B5>0,"Expected "&D5-B5&" mins Late",IF(D5<B5,"EXPECTED p mins ?&B5-D5&? Early?,?on time?))<>
-B5<0,"Expected "&B5-D5&" mins Early","on time"))
Hope this helps

 

[AtlantaCargo]

Thanks for all the help guys!!!

It's kind of difficult to explain, but I think creating a macro is the best way to go....

the only problem is i have no idea. I received some help from a guy named ravi...he pushed me in the right direction, but i don't know how to write a macro.

Here's the actual sheet....

<TABLE style="WIDTH: 427pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=570 border=0 x:str><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 987" width=27><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 4681" width=128><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 987" width=27><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 987" width=27><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><TBODY><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 65pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: black" width=86 height=22>Pushback</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 88pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: black" width=117>Entered Deice</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 20pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: gray" width=27>∆1</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 96pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: black" width=128>Departed Deice</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 20pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: gray" width=27>∆2</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: black" width=79> Airborne</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 20pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: gray" width=27>∆3</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: black" width=79>Total Time</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffcc" height=18 x:num="0">0:00</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" x:num="2.0833333333333332E-2">0:30</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696; mso-ignore: style" x:num="2.0833333333333332E-2" x:fmla="=(B2-A2)">0:30</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" x:num="6.25E-2">1:30</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-LEFT: windowtext; COLOR: red; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696; mso-ignore: style; text-underline-style: none" x:num="4.1666666666666671E-2" x:fmla="=(D2-B2)">1:00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #00ccff" x:num="0.125">3:00</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-LEFT: windowtext; COLOR: red; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696; mso-ignore: style; text-underline-style: none" x:num="6.25E-2" x:fmla="=(F2-D2)">1:30</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: red; BORDER-LEFT: windowtext; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" x:num="0.125" x:fmla="=(F2-A2)">3:00</TD></TR></TBODY></TABLE>

as you can see, i have ^1, 2 & 3 measuring the progress times and will flag (using conditional formatting) any times greater than X mins....

what i need is a cell "flagged" if it's greater than X mins, without entering a time....

say under "Depart Deice", i hadn't input a time yet (because i'm going to be busy), i need it to tell me when that certain airplane hadn't made it to that point within the allotted time.

IS THIS POSSIBLE????

Ravi had written a macro where i just clicked a button and it showed me all of the airplanes that were delayed....without having any times typed in.

I'd really appreciate any help! I can also send you a blank copy of the spreadsheet...


Cheers!

Wes McDonald, Airside Operations Agent
wesley.mcdonald@atlanta-airport.com​