Skip to main content

Using IF statement to compare timestamp against NOW

  • October 16, 2023
  • 5 replies
  • 93 views
Z
Forum|alt.badge.img+4

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

Databaser
Forum|alt.badge.img+25
  • Databaser
  • Brainy
  • October 16, 2023

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
Jan - Databaser
Z
Forum|alt.badge.img+4
  • zuilkowski_
  • Author
  • New Participant
  • October 16, 2023

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?

Databaser
Forum|alt.badge.img+25
  • Databaser
  • Brainy
  • October 16, 2023

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

Jan - Databaser
Z
Forum|alt.badge.img+4
  • zuilkowski_
  • Author
  • New Participant
  • October 17, 2023

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
Forum|alt.badge.img+25
  • Databaser
  • Brainy
  • October 17, 2023

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

Source

Jan - Databaser
Terms & ConditionsAccessibility statement