Help

Conditional comparing Date time with NOW() does not evaluate time

Topic Labels: Dates & Timezones
Solved
Jump to Solution
3496 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Carlos_Seguin-L
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi team,

Here’s the context
2 fields:
Scheduled date: A Date field with ISO format, 24h format, it includes Time and uses GMT for all collaborators

Date validation: A formula field that runs the following code:

IF(
  AND(
    {Scheduled date},
    NOW() >= {Scheduled date}
  ),
  "Ready", "In Review"
)

Problem:
When Scheduled date = Today, the formula returns Ready, ignoring the time.

For instance:
Case A

  • Scheduled date = 2022-08-23 18:00 GMT
  • NOW() = 2022-08-23 19:00 GMT

The formula returns Ready :x:

Case B

  • Scheduled date = 2022-08-24 18:00 GMT
  • NOW() = 2022-08-23 19:00 GMT

The formula returns In Review :white_check_mark:

Could it be that NOW() doesn’t care about time? or is it using a different Time Zone?

1 Solution

Accepted Solutions
Carlos_Seguin-L
5 - Automation Enthusiast
5 - Automation Enthusiast

Solved!

Problem was the linked date field from another table, Scheduled Date, did not bring with it its format (ISO, GMT, include time…) so I needed to add a lookup field with the date and it worked!

Captura de Pantalla 2022-08-23 a las 18.37.13

See Solution in Thread

2 Replies 2
Carlos_Seguin-L
5 - Automation Enthusiast
5 - Automation Enthusiast

Not sure what’s going on but NOW() does evaluate the time. See hereNOW() reference

I run a few tests. It works fine when Scheduled Date is a field in the same table.

In my case Scheduled Date is a linked field from a Time Slots table. When I set the linked field to a future date it immediately evaluates to true/Ready :man_shrugging:t4:

Carlos_Seguin-L
5 - Automation Enthusiast
5 - Automation Enthusiast

Solved!

Problem was the linked date field from another table, Scheduled Date, did not bring with it its format (ISO, GMT, include time…) so I needed to add a lookup field with the date and it worked!

Captura de Pantalla 2022-08-23 a las 18.37.13