# Re: Using IF statement to compare timestamp against NOW

773 0
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hey folks,

I am currently working on a formula to compare the current time against a timestamp that's populating into my table from a separate service.

I am also attempting to create multiple columns that iterate on my formula by comparing NOW with DATEADD +5 "hours" to check if NOW is 5 hours since the time in the timestamp.

Having some trouble figuring out what I'm doing wrong with this formula and hoping someone can point me in the right direction:

``````IF(
DATETIME_FORMAT(NOW(), "MM/DD HH:SS") > DATETIME_FORMAT(DATEADD({Timestamp}, "MM/DD HH:SS"), +5, "hours", "MM/DD HH:SS"),
"yes",
"no")``````

5 Replies 5
12 - Earth

Just a heads up, but the NOW() function is very taxing on large(r) bases. TODAY() is in that regard a better option. (source)

This formula should work:

IF(DATETIME_FORMAT(NOW(), "MM/DD HH:SS")>DATETIME_FORMAT(DATEADD({Timestamp}, 5, "hours"), "MM/DD HH:SS"),"yes",  "no")
• there can' be spaces in front of and after ">" to compare both "datetimes"
• the "+5" should be just "5" and should be added inside the DATEADD() function, along with the "hours"
• no need for the "MM/DD HH:SS" inside the DATEADD() function
5 - Automation Enthusiast

Great tip about NOW(). I updated that to TODAY. Thank you!

Also removed the "MM/DD HH:SS" inside the DATEADD function and the "+" from the "5". Lastly I removed the spaces as well.

I turns out I think I was missing a closing parenthesis that was stopping the formula from resolving. 😅

@Databaser would you be open to reviewing my setup and confirming my logic?

---

The timestamp is coming in with this format and i am storing it in a plain text field.

I then have three additional columns that are checking against the timestamp.

1. Is the current date/time +3 hours from the date/time of the timestamp

``````IF(
DATETIME_FORMAT(TODAY(), "MM/DD HH:SS")>DATETIME_FORMAT(DATEADD({Timestamp}, 3, "hours", "MM/DD HH:SS")),
"yes",
"no")``````

2. Is the current date/time +5 hours from the date/time of the timestamp

``````IF(
DATETIME_FORMAT(TODAY(), "MM/DD HH:SS")>DATETIME_FORMAT(DATEADD({Timestamp}, 5, "hours", "MM/DD HH:SS")),
"yes",
"no")``````

3. Is the current date/time +10 hours from the date/time of the timestamp

``````IF(
DATETIME_FORMAT(TODAY(), "MM/DD HH:SS")>DATETIME_FORMAT(DATEADD({Timestamp}, 10, "hours", "MM/DD HH:SS")),
"yes",
"no")``````

Since it is currently 10:47am where I am I would imagine that timestamp should trigger both the first and second statements right? Or is there something I'm missing?

12 - Earth

I think so, yes. I also think you should close your DATEADD sooner.

So: DATETIME_FORMAT(DATEADD({Timestamp}, 3, "hours"), "MM/DD HH:SS")

iso DATETIME_FORMAT(DATEADD({Timestamp}, 3, "hours", "MM/DD HH:SS"))

5 - Automation Enthusiast

Thanks @Databaser that also helped the formula resolve. However I wanted to visually confirm that the date and time are being recorded correctly and when I strip everything away and just run this formula:

``DATETIME_FORMAT(TODAY(), "MM/DD HH:SS")``

I am getting this result where the Hour and Second are do not appear to be registering. Do you know if this could be a formatting issue with the way I structured date/time format?

12 - Earth

"While similar to the NOW() function: TODAY() returns the current date (not the current time, if formatted, time will return 12:00am)."

Source