Date calculations to determine "yesterday"

[Sharon_Visser]

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.

[TheTimeSavingCo]

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"
)

And here’s a link to the base

The base has the Today field set up as a date field purely so that we can see the formula at work

[ScottWorld · Accepted solution]

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')

[Sharon_Visser]

Thanks @Adam_TheTimeSavingCo - works perfectly!

[Sharon_Visser]

@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.

[TheTimeSavingCo]

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!

[ScottWorld]

Sharon_Visser:

Can I mark 2 responses as the solution, both your’s and the one submitted by @Adam_TheTimeSavingCo?

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!