Using IF statement to compare timestamp against NOW

[zuilkowski_]

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")

[Databaser]

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_]

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?

[Databaser]

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_]

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? 

[Databaser]

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

Source