Completed within due or out of due date

Topic Labels: Formulas
Solved
1685 4
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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:

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.

1 Solution

Accepted Solutions

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(
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?

4 Replies 4
18 - Pluto

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

The formula in use is:

``````IF(
"Completed on time",
"Not completed on time"
)
``````
5 - Automation Enthusiast

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?

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(
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?

5 - Automation Enthusiast