Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 16, 2022 01:32 AM
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:
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.
Solved! Go to Solution.
Nov 16, 2022 04:03 AM
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?
Nov 16, 2022 02:36 AM
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"
)
Nov 16, 2022 03:45 AM
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?
Nov 16, 2022 04:03 AM
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?
Nov 16, 2022 04:55 AM
Perfect, Adam!! You nailed it!
It’s cleaner and finally working properly. Thank you very much for your help!