Need help to use countifs function to count number of delayed and ontime automatically?

Hi Smartsheet team,

Is there a way for smartsheets to count the on time or delayed? As i have this data file and with an ETD and ETA on different dates and wanting to know what is the right formula to use or what changes are required for me to count the same?


  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭


    Hi Robert

    Just so that I can help out here, I need to clarify a couple of things if I may.

    If you want to know if something Departed on Time, which Date Column is storing the scheduled Departure Date? (I assume the ETD is the Actual Departure Date)

    If you want to know if something is Arriving on Time, which Date Column is storing the scheduled Arrival Date? (Again assuming the ETA is the Actual Arrival Date).

    You could then set up 2 helper columns, 1 for Departure Status the other for Arrival Status with "Early", "Ontime" and "Late" as options (or alternative words of your choosing) and we can use a simple IF function to set the values by comparing the Scheduled date with the Actual Date for each Departure and Arrival. (these can be column formulae and hidden from the sheet)

    Then you are just needing to set up Countif Departure Status is "Early", etc.

    Happy to help further with the correct columns identified...



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!