Showing Completion of tasks ON TIME: EARLY: LATE

Brian Beatty

I work for an air cargo company. I seek help with calculating the above. Here is an overview of a typical nights work out on the ramp.

A load planner and cargo loading team is tasked with getting airplanes loaded and sent out on time every night. If they are early, great! If they are on time, great! If they are late, heads roll!

The load planner is tasked with filling out this flight information manually, using smartsheet before the Air Craft arrival. The landing and departure times are manually entered in real time when they occur.

I am trying to show if they are on time, early, or late using military time.

Here are the constants we have: Scheduled time to land. Scheduled time to depart.

I have attached what I have so far in both list and grid view. Each "card" is an actual parking gate where an AC resides at the terminal.

There are currently no formulas in this sheet. Some conditional formatting only.

Can anyone please help point me in the right direction with a formula to help auto calculate these times per flight?

Perhaps when they place the military time in the ATA (actual time of arrival), it will calculate and show actual + or - late minutes based on what the ETA was?

When time is entered in the ATD (actual time of departure), it will calculate and show actual + or - minutes based on what the ETD was?

Paul Newcome

You would need to first convert each of the times into numbers.

=VALUE(LEFT(ETA@row, 2)) + (VALUE(RIGHT(ETA@row, 2)) / 60)

Do that for each of your time columns, and you will have numbers that you can compare using an IF statement such as

=IF([ATA Help]@row > [ETA Help]@row, "Late", IF([ATA Help]@row = [ETA Help]@row, "On Time", "Early"))

PeterUser

Hi, I am wondering why there is no preset setting like in MS Project showing the status Late/OnTime. I should be as is easy as just picking up the column, and choosing an option rather than having to work this out using formulas. I was hoping smartsheet is more innovative than this. Unless I am missing some options as a newbie.