Help

Display a message if today's date is in a linked field

Topic Labels: Formulas
Solved
Jump to Solution
1294 8
cancel
Showing results for 
Search instead for 
Did you mean: 
AIC
6 - Interface Innovator
6 - Interface Innovator

Hi all,

Please see the screenshot below - I’d like to display a message in the Is Absent Today column if today’s date is among the dates in Absences.

Currently I’m using the following formula but it only works if there’s a single date:

IF(DATETIME_DIFF({Absences}, TODAY(), 'days') < 1, "Yes", BLANK())

Any help is much appreciated!

image

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

Try replacing TODAY() with NOW()

See Solution in Thread

8 Replies 8

This can be done using the FIND() function. Since you’re working with dates this could be tricky. Your date field appears to use the 'MMMM D, YYYY' format. So, you want to test if Today’s date can be “found” in the {Absences} field. I included the SET_TIMEZONE() function in my formula below, but you may need to adjust the timezone to the one that matches your location.

IF(
   FIND(
      DATETIME_FORMAT(SET_TIMEZONE(NOW(), 'America/Los_Angeles'), 'MMMM D, YYYY'), 
      {Absences}
   ),
   "Yes"
)

Edited: Formula edited to replace TODAY() with NOW()

Thanks! That doesn’t quite seem to work. It’s behaving erratically:

image

Is the primary field of whatever table {Absences} links to an actual date field? Did you adjust for the correct timezone?

Yes, Absences is linked to these rows in another table:

image

And did you do that^?

Yup! I’m not sure why this isn’t working, I’ve tried several different formulas/formats. It’s quite odd.

Kamille_Parks
16 - Uranus
16 - Uranus

Try replacing TODAY() with NOW()

That did it! Thank you!!

image