- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 23, 2021 01:22 PM
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!
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 24, 2021 07:38 AM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 23, 2021 05:16 PM
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()
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 24, 2021 06:20 AM
Thanks! That doesn’t quite seem to work. It’s behaving erratically:
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 24, 2021 07:13 AM
Is the primary field of whatever table {Absences}
links to an actual date field? Did you adjust for the correct timezone?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 24, 2021 07:26 AM
Yes, Absences
is linked to these rows in another table:
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 24, 2021 07:28 AM
And did you do that^?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 24, 2021 07:30 AM
Yup! I’m not sure why this isn’t working, I’ve tried several different formulas/formats. It’s quite odd.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 24, 2021 07:38 AM
Try replacing TODAY()
with NOW()
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 24, 2021 07:40 AM
That did it! Thank you!!