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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

GlennUK

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

Watch MrExcel Video

Forum statistics

Threads
1,133,553
Messages
5,659,477
Members
418,504
Latest member
kingman29

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