
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 31, 2022 12:27 PM
Hi all - I know dates and timezones are complex, and I’ve tried to do my homework but I’m really stumped. My end goal is to identify records created yesterday and send off a notification. To start, I’m attempting to calculate how many days are between Created Date (w/o time) and Today (without time). Ideally, I would say if Today - Created Date = 1, then that record was created yesterday and I can filter on those records in a view.
Trial 1:
- ‘Created Date’ is set to use GMT for all collaborators, and does not include time
- I created a formula variable called ‘Today’ with formula “TODAY()”, which is set to use GMT for all collaborators and does not include time. The value shows as 8/31/22.
- In a formula field I compared the 2 dates: DATETIME_DIFF(Today,{Created Date},‘days’)
- For both yesterday and today, the value of that comparison was zero
- with Detail: today is 8/31/22. The record created yesterday on 8/30/22 had a calculated value of 0. The record created today on 8/31/22 had a calculated value of 0.
Trial 2: (w/o GMT)
- ‘Created Date’ does not use GMT and displays with EDT
- The ‘Today’ variable that uses TODAY() does not use GMT and displays with EDT. But the value of Today now shows as 8/30/22 (which is not Today in EDT)
- On the same DATETIME_DIFF formula above, both records created yesterday and today show 0.
Any help would be appreciated.
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 01, 2022 04:49 AM
Hi @Sharon_Visser,
The IS_SAME
function lets you compare if two dates are the same date, up to any unit that you specify.
So, if you keep both of your fields as GMT, then your formula could look like this:
IS_SAME(
DATEADD(TODAY(),-1,'days'),
{Created Date},
'days')
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 31, 2022 09:44 PM
Hi Sharon, yeah, I feel like using DATETIME_DIFF
might not be the way to go here. Don’t get me wrong, totally doable, but thinking about all the edge cases around that made my head hurt
If I were you, I think I would use a formula to check whether the created date matched yesterday’s date instead, and I’ve attached the formula below
IF(
DATEADD(Today, '-1', 'days')
=
DATETIME_PARSE(
DATETIME_FORMAT(
Created,
'DD MM YYYY'
),
'DD MM YYYY'
),
"Yesterday"
)
The base has the Today
field set up as a date field purely so that we can see the formula at work

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 01, 2022 04:49 AM
Hi @Sharon_Visser,
The IS_SAME
function lets you compare if two dates are the same date, up to any unit that you specify.
So, if you keep both of your fields as GMT, then your formula could look like this:
IS_SAME(
DATEADD(TODAY(),-1,'days'),
{Created Date},
'days')

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 01, 2022 06:04 AM
Thanks @Adam_TheTimeSavingCo - works perfectly!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 01, 2022 06:07 AM
@ScottWorld, this works perfectly also!
Can I mark 2 responses as the solution, both your’s and the one submitted by @Adam_TheTimeSavingCo? If I can only mark 1 as the solution, I’ll go with Adam’s since he submitted his first. But I definitely learned something helpful from both of you.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 01, 2022 07:09 AM
Hi Sharon, I’m glad I could help!
I think that you should mark Scott’s reply as the solution as it’s much more elegant than mine though!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 01, 2022 07:33 AM
Lol, you can only mark one solution, but feel free to mark whichever one you want! Like many things in Airtable, there are often 3 or 4 or 5 different ways of accomplishing the very same goal. Sometimes there are even MORE ways of accomplishing the same goal!
