Completed within due or out of due date

[Aline_Branco_Wi]

Hi everyone! After checking several topics I still couldn’t find something to solv the problem I’m facing, so here I am. We have a table called Tasks and the following fields are important for this:

• Due Date (Date field)
• Completed (Checkbox)
• Completed within due date (formula)

We added a Last modified time field to look to Completed field, so it returns the date that collaborator finished the task. To compare the date that Completed was checked with the due date, I tried the following:

IF({Due Date} = {Last modified time},“1”,
IF({Due Date} > {Last modified time},“1”,
IF({Due Date} < {Last modified time},“0”)))

The problem is even if Due Date and Last time modified has the same date, the formula field is returning 0 and my guess is beacause of the time. Due date was not created with time - because it doesn’t metter for us - but if we allow time, it shows “11/16/2022 00:00”. So Last time modified will always be after due date since we will always finish our task after 00:00.
And apperently it doesn’t metter if we choose to display time or not - behind the scenes, airtable computes the time.

Can anyone help me to only get the date in the formula field?

PS.: I also tried using the functions IS_SAME(), IS_BEFORE() and IS_AFTER() but this doesn’t attack the problem.

[TheTimeSavingCo]

Hi Aline, I’ve set something up here for you to check out that may help

The formula in use is:

IF(
  IS_BEFORE({Last Modified}, {Due Date}),
  "Completed on time",
  "Not completed on time"
)

[Aline_Branco_Wi]

Hi, Adam!! Thanks for your reply =]

I guess I wasn’t clear in my post, so I’m sorry for that. This way you made is one of the ways I tried. Is there anyway we could do this considering only the date and not the time?

Just to clarify, I’m using 1 and 0 as a return because it will be used in an automation and will be an easier parameter to insert there, but logic is the same or am I missing something?

[TheTimeSavingCo · Accepted solution]

Hi Aline! I’m so sorry, you were perfectly clear in your post, I misunderstood you

I’ve updated the original base:

And this is the new formula:

IF(
  IS_BEFORE(
    {Last Modified}, 
    DATETIME_PARSE(
      DATETIME_FORMAT(
        {Due Date},
        "DD MM YYYY"
      ) & "2359",
      "DD MM YYYY hhmm"
    )
  ),
  "Completed on time",
  "Not completed on time"
)

I think this should be right?

[Aline_Branco_Wi]

Perfect, Adam!! You nailed it!
It’s cleaner and finally working properly. Thank you very much for your help!