Skip to main content
Solved

Date calculations to determine "yesterday"

  • August 31, 2022
  • 6 replies
  • 105 views
S
Forum|alt.badge.img+13

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.

Best answer by ScottWorld

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

    6 replies

    TheTimeSavingCo
    Forum|alt.badge.img+31
    • TheTimeSavingCo
    • Brainy
    • 6460 replies
    • September 1, 2022

    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

    Adam, Airtable Consultant - thetimesaving.company
    ScottWorld
    Forum|alt.badge.img+35
    • ScottWorld
    • Genius
    • 9808 replies
    • Answer
    • September 1, 2022

    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')
    S
    Forum|alt.badge.img+13
    • Sharon_Visser
    • Author
    • Inspiring
    • 39 replies
    • September 1, 2022

    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


    Thanks @Adam_TheTimeSavingCo - works perfectly!

    S
    Forum|alt.badge.img+13
    • Sharon_Visser
    • Author
    • Inspiring
    • 39 replies
    • September 1, 2022

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

    @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
    Forum|alt.badge.img+31
    • TheTimeSavingCo
    • Brainy
    • 6460 replies
    • September 1, 2022

    Thanks @Adam_TheTimeSavingCo - works perfectly!


    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!

    Adam, Airtable Consultant - thetimesaving.company
    ScottWorld
    Forum|alt.badge.img+35
    • ScottWorld
    • Genius
    • 9808 replies
    • September 1, 2022

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


    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!


    Terms & ConditionsAccessibility statement