Skip to main content

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

 

 

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

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

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?

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

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? 

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

Source

Reply


Terms & Conditions