Oct 15, 2023 08:46 PM
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")
Oct 16, 2023 07:20 AM
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:
Oct 16, 2023 07:48 AM
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?
Oct 16, 2023 09:02 AM
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"))
Oct 17, 2023 08:46 AM
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?
Oct 17, 2023 09:37 AM
"While similar to the NOW() function: TODAY() returns the current date (not the current time, if formatted, time will return 12:00am)."