Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Using IF statement to compare timestamp against NOW

Topic Labels: Formulas
2200 5
cancel
Showing results for 
Search instead for 
Did you mean: 
zuilkowski_
5 - Automation Enthusiast
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
Databaser
12 - Earth
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
zuilkowski_
5 - Automation Enthusiast
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. 

Screenshot 2023-10-16 at 10.47.13 AM.png

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?

Databaser
12 - Earth
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"))

zuilkowski_
5 - Automation Enthusiast
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? 

Screenshot 2023-10-17 at 11.46.02 AM.png

Databaser
12 - Earth
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