Skip to main content
Solved

Compare NOW() or TODAY() with date field in a table

  • May 16, 2020
  • 4 replies
  • 113 views
P
Forum|alt.badge.img+10

Hi :slightly_smiling_face:

I want to create formula that will return true if today is after date or s within two dates.
IF(AND(NOW()>{Check-in},NOW()<{Check-out}),“NOW”,"")

Above is the formula that does not work for me, it always returns false.

I would appreciate some help. Thanks

Best answer by kuovonne

When comparing date/time values, it is better to use the date/time comparison functions.
You can use the IS_BEFORE, IS_AFTER, and IS_SAME functions, or you can use the DATETIME_DIFF function.

IF(
  AND(
    DATETIME_DIFF(TODAY(), {Check-in}, 'days') >= 0,
    DATETIME_DIFF(TODAY(), {Check-out}, 'days') <= 0
  ),
  TRUE(),
  FALSE()
)

A couple of notes:

  • TODAY() and NOW() are not continuously updated. You may need to reload a base or view in order to update them.

  • TODAY() and NOW() return the GMT date and time, not local time. So if your {Check-in} and {Check-out} times are displayed as local dates, your results might be off by a day.

4 replies

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • May 16, 2020

Your formula works just fine for me. Be sure that you’re using “straight quotes” instead of “curly quotes”, so that your formula looks like this:

IF(AND(NOW()>{Check-in},NOW()<{Check-out}),"NOW","")

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • Answer
  • May 17, 2020

When comparing date/time values, it is better to use the date/time comparison functions.
You can use the IS_BEFORE, IS_AFTER, and IS_SAME functions, or you can use the DATETIME_DIFF function.

IF(
  AND(
    DATETIME_DIFF(TODAY(), {Check-in}, 'days') >= 0,
    DATETIME_DIFF(TODAY(), {Check-out}, 'days') <= 0
  ),
  TRUE(),
  FALSE()
)

A couple of notes:

  • TODAY() and NOW() are not continuously updated. You may need to reload a base or view in order to update them.

  • TODAY() and NOW() return the GMT date and time, not local time. So if your {Check-in} and {Check-out} times are displayed as local dates, your results might be off by a day.

D
P
Forum|alt.badge.img+10
  • Pjero_Kusijanov
  • Author
  • Known Participant
  • May 17, 2020

Thank you both on your help. @ScottWorld Unfortunately the formula does not work for me, it may be because it’s Lookup field.

@kuovonne I manage to get this to work with IS_BEFORE and IS_AFTER.

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • May 17, 2020

Thank you both on your help. @ScottWorld Unfortunately the formula does not work for me, it may be because it’s Lookup field.

@kuovonne I manage to get this to work with IS_BEFORE and IS_AFTER.


@Pjero_Kusijanovic Oh okay, you didn’t mention that it was a Lookup field… key piece of information there! :winking_face: Glad that you got it working with the other Date functions that are available to you.

Terms & ConditionsAccessibility statement