Help

Comparing a date field to a date I input in a formula?

Topic Labels: Dates & Timezones Formulas
363 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Revolutions_AI
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi everyone,

I have been struggling with comparing a date of when a record has been created to a date I am manually inputing into the formula. So basically I am trying to use the IS_AFTER function but have tried so many things and just everything is either coming up with error or false to all fields even though i know some of them are after the date i am specifying.

I am trying to get all records that are true to being created after the 21/10/2024 and feed them into a make.com automation using this field or formula.

This is the formula I have been using but all the fields are showing up as 0 (false) when there are definitely some records created after the 21/10/2024. I had to use the date format with the date parse as it was automatically formatting the date in US format even though I have specified the format?

 

IS_AFTER(DATETIME_FORMAT(Created, "DD/MM/YYYY hh:mma"), DATETIME_FORMAT(DATETIME_PARSE("21/10/2024 00:01am", "DD/MM/YYYY hh:mma"), "DD/MM/YYYY hh:mma"))
 
I also tried formatting both dates like in the photos below but still not getting the result I am after, any help would be much appreciated am i missing something simple or just over complicating the whole thing?
 
Screenshot 2024-10-25 at 12.24.03 PM.pngScreenshot 2024-10-25 at 3.01.51 PM.png

 

4 Replies 4

Hello,

You do not need to change the date format within the formula, all you need to do is to input the compared date '21/10/2024' as '10/21/2024':

Pascal_Gallais_4-1729850386490.png

You should get the following result:

Pascal_Gallais_5-1729850434119.png

Regards,

Pascal

 

 

 

Hey Pascal,

I thought I was over complicating the hell out of it. 

so just to confirm when using formulas with dates and times if you write a formula manually it has to be in US format for it to work?

@Pascal_Gallais-   Hmm, I think there's some sort sort of interaction between the format of the Date field and the format we key in to the formula?  The formula didn't give provide the expected results, and I think it's because my Date field is set to "Friendly"

Screenshot 2024-10-26 at 2.41.46 PM.png
What do you think of us doing a single DATETIME_PARSE() at the end?

IS_AFTER(
  Date,
  DATETIME_PARSE(
    "21/10/2024",
    "DD/MM/YYYY"
  )
)

 

Alexey_Gusev
13 - Mars
13 - Mars

Hi,
You can Use ISO format in formula to avoid mixing day and month.
It's the format with constant length (10 chars without time, '2024-10-27') and it can be sorted as string in correct order.

Alexey_Gusev_0-1729992544879.png

Anyway, it's better to use special date functions, than usual math operators. I mean, using 1st foirmula is OK. Using 2nd - OK, but check if possible, especially. 2024-09-21