+ Reply to Thread
Results 1 to 10 of 10

Help - Calculating Time Differences (on time, early or late flights)

  1. #1
    Registered User
    Join Date
    03-27-2019
    Location
    UK
    MS-Off Ver
    2013
    Posts
    5

    Question Help - Calculating Time Differences (on time, early or late flights)

    Hi all,

    I am struggling with x2 formulas in regards to calculating if a Flight is either: Early (if so, how early in mins : secs), On Time (needs to be included in the early column) or Late (if so, how late in mins : secs):

    excel_timehelp2.JPG

    What I would like the data to show (inc. important notes):

    1) Columns D and G are formatted as custom "hh:mm:ss" ... and ... columns J and K are formatted as custom "mm:ss"

    2) I need an "on time" flight to be included in the "early" column

    3) I need the main formulas to be inside IF(ISERROR([formula])),"",[formula]) as I need blank records if data cannot be found elsewhere (to avoid N/A results)

    3) A flight is considered on time / early between e.g. 12:54:00 - 12:54:59 if due at 12:54:00

    4) A flight is considered late if it is after e.g. 12:54:59 (therefore 12:55:00+) if due at 12:54:00

    E.g. flight AIJ6979 (highlighted) was due to takeoff at 12:54:00. He took off at 12:54:59 so he is on time yet my formulas are calculating that this flight is late by 00:00.

    Current formulas:

    As you can see I thought that ...

    1) To find out if a flight had taken off on time or early, it was a simple case of subtracting the "actual time" from the "due time" - adding 59 seconds for leeway using the TIME function. As you can see for the highlighted flight AIJ6979, my formulas are calculating that this flight is late, even though this flight is on time (just - i.e. by 0 second).

    2) To find out if a flight was late taking off, it was a simple case of subtracting the "due time" from the "actual time" - subtracting 59 seconds for leeway using the TIME function. For example if we change flight AIJ6979 to have an "Actual TO time" of 12:55:00, the late and early formulas work fine and as should.

    Column J: =IF(ISERROR($M38-$P38+TIME(0,0,59)),"",$M38-$P38+TIME(0,0,59))

    Column K: =IF(ISERROR($P38-$M38-TIME(0,0,59)),"",$P38-$M38-TIME(0,0,59))

    Should I be using an IF statement? If so, I have no idea where to start as my mental health isn't too great at the moment so am unable to concentrate

    Please help me fix this because I am unsure why this has provided such results
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    03-19-2019
    Location
    London
    MS-Off Ver
    2003-2016/2016 Mac
    Posts
    57

    Re: Help - Calculating Time Differences (on time, early or late flights)

    Use this formula in J2

    Please Login or Register  to view this content.
    and this in K2

    Please Login or Register  to view this content.
    and drag down

  3. #3
    Registered User
    Join Date
    03-27-2019
    Location
    UK
    MS-Off Ver
    2013
    Posts
    5

    Unhappy Re: Help - Calculating Time Differences (on time, early or late flights)

    Many thanks for your help but these do not provide the results I am after.

    I've attached the edited version including the formulas above for your reference
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-19-2019
    Location
    London
    MS-Off Ver
    2003-2016/2016 Mac
    Posts
    57

    Re: Help - Calculating Time Differences (on time, early or late flights)

    Change Custom Format from Cells from mm:ss;;; to mm:ss

  5. #5
    Registered User
    Join Date
    03-27-2019
    Location
    UK
    MS-Off Ver
    2013
    Posts
    5

    Re: Help - Calculating Time Differences (on time, early or late flights)

    Quote Originally Posted by Saqib Qureshi View Post
    Change Custom Format from Cells from mm:ss;;; to mm:ss
    Back to my original problem still then.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Help - Calculating Time Differences (on time, early or late flights)

    Try the following:
    Early by: =IF(G2<D2+TIME(0,1,0),D2+TIME(0,0,59)-G2,"")
    Late by: =IF(G2>=(D2+TIME(0,1,0)),G2-(D2+TIME(0,0,59)),"")
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    03-27-2019
    Location
    UK
    MS-Off Ver
    2013
    Posts
    5

    Re: Help - Calculating Time Differences (on time, early or late flights)

    Quote Originally Posted by JeteMc View Post
    Try the following:
    Early by: =IF(G2<D2+TIME(0,1,0),D2+TIME(0,0,59)-G2,"")
    Late by: =IF(G2>=(D2+TIME(0,1,0)),G2-(D2+TIME(0,0,59)),"")
    Let us know if you have any questions.
    Hi JeteMc,

    Appreciate your reply.

    I tried your formulas above. Your formula for late works but as like me, the early doesn't work as required - it's to do with the "on time" flights.

    I've continued working on this in my spare time and come up with the following:

    Early by: =IF($G2<$D2,$D2-$G2,IF($G2<($D2+TIME(0,1,0)),$G2-$D2,""))

    Late by: =IF($G2>($D2+TIME(0,0,59)),$G2-IF($G2>$D2+(TIME(0,0,59)),$D2+TIME(0,0,59),""))

    My "late by" formula works perfectly (as does yours).

    However my "early by" always calculates "bang on time" flights as 00:59 seconds early. See attached
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Help - Calculating Time Differences (on time, early or late flights)

    Open the timedifferences_excelforum.xlsx file and select cell J37. Now run the Evaluate Formula feature. The formula evaluates to zero as it should if my understanding is correct.
    Now look at the formatting for that cell which is mm:ss;;;
    That formatting suppresses the display of zero.
    Change the format to mm:ss
    J37 will now display 00:00 or at least it does on my computer
    Let us know if you have any questions.

  9. #9
    Registered User
    Join Date
    03-27-2019
    Location
    UK
    MS-Off Ver
    2013
    Posts
    5

    Cool Re: Help - Calculating Time Differences (on time, early or late flights)

    Hi,

    Apologies on the late reply. I gave platelets on Friday so had to recover for a few days.

    I changed the formatting of the cells and your formulas you provided seem to work perfectly with my test data (which should cover all scenarios).

    Thanks for your help. I really appreciate it!

    I attached the test data sheet using your formulas (edited for N/A data purposes).

    Pete
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Help - Calculating Time Differences (on time, early or late flights)

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 05-20-2023, 01:07 AM
  2. On time, late, early if statement
    By TehJonny in forum Excel General
    Replies: 3
    Last Post: 04-05-2016, 05:05 AM
  3. Time Caculations late/early and summary
    By tobiahr in forum Excel General
    Replies: 9
    Last Post: 06-10-2015, 09:33 PM
  4. Replies: 6
    Last Post: 05-07-2015, 12:53 PM
  5. [SOLVED] Graph to chart how many times different vendors are early, on time, or late...
    By ekf23 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 04-18-2014, 03:46 PM
  6. Formula to Display Notification LATE, EARLY, ON TIME
    By Daryl10 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-12-2014, 06:27 PM
  7. [SOLVED] IF function: Early/On Time/Late Time vs. set window of time
    By hclark579 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-21-2014, 05:37 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1