Need help with a time function

AtlantaCargo

New Member
Joined
Oct 15, 2008
Messages
2
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,184
*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

New Member
Joined
Oct 15, 2008
Messages
2
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

New Member
Joined
Oct 15, 2008
Messages
2
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
 
Last edited:

AtlantaCargo

New Member
Joined
Oct 15, 2008
Messages
2
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
 

Forum statistics

Threads
1,081,798
Messages
5,361,373
Members
400,629
Latest member
ganeshkhatri

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top