Skip to main content

Date check formula

  • May 12, 2024
  • 4 replies
  • 84 views
V
Forum|alt.badge.img+3

I am using a formula cell to check a date/time cell to confirm if the time in EST has occurred or not yet.

The Date/Time cell contains something like 5/12/2024 2:30pm EST

My Formula cell uses the formula:

IF(
AND(
{Start Date},
DATEADD({Start Date}, -5, 'hours') <= DATEADD(NOW(), -5, 'hours')
),
"true",
BLANK()
)

What I'd like to happen is for the Formula cell to return "true" when the Date/Time cell reads a time that is equal to or before the current Easter Standard Time.  Note: I don't want it to use my current time zone because that can change based on where I am.

However, the problem I'm having is that it seems to be using my current time zone (which is CST).

    4 replies

    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • May 13, 2024

    Date fields are always stored in GMT, even if they are displayed as a different time zone. The same goes for NOW(). NOW() is GMT under the hood, even if it is displayed as a different timezone. Thus, you do not need to add an offset when testing to see which date/time is earlier. 

    It is also good practice to compare date/time values using the functions designed for comparing dates versus the mathematical operators (like <=) which can produce unexpected results.

    Also when the "else" part of your IF() function is blank, you can simply omit it.

    Try this.

    IF( AND( {Start Date}, IS_BEFORE({Start Date}, NOW()) ), "{Start Date} is before NOW()" )

    On the other hand, I also recommend avoiding the use of NOW(). It is not very accurate--when the base is open it is recalculated about every 5-10 minutes, and when the base is closed, it might not recalculate for an hour or more. It also recalculates for every record in the table, which can slow down a base with lots of records.

    If your {Start Date} is a date only (no time), you might want to consider using a filtered view based on the {Start Date} instead of a formula field. 

    V
    Forum|alt.badge.img+3
    • vaultsportshq
    • Author
    • New Participant
    • May 13, 2024

    Date fields are always stored in GMT, even if they are displayed as a different time zone. The same goes for NOW(). NOW() is GMT under the hood, even if it is displayed as a different timezone. Thus, you do not need to add an offset when testing to see which date/time is earlier. 

    It is also good practice to compare date/time values using the functions designed for comparing dates versus the mathematical operators (like <=) which can produce unexpected results.

    Also when the "else" part of your IF() function is blank, you can simply omit it.

    Try this.

    IF( AND( {Start Date}, IS_BEFORE({Start Date}, NOW()) ), "{Start Date} is before NOW()" )

    On the other hand, I also recommend avoiding the use of NOW(). It is not very accurate--when the base is open it is recalculated about every 5-10 minutes, and when the base is closed, it might not recalculate for an hour or more. It also recalculates for every record in the table, which can slow down a base with lots of records.

    If your {Start Date} is a date only (no time), you might want to consider using a filtered view based on the {Start Date} instead of a formula field. 


    This is helpful, thank you!  A couple of other questions stemmed from your response.

    1. If all times are read in GMT by the Airtable automation, then should I just set all my times to be in GMT as to not confuse myself (see image below), and when I need to insert a time that is, for example, in EST, I should convert it to GMT first?  (i.e. if the time I need to put into airtable is 8:30am EST, I should first convert it to GMT, so 12:30pm GMT?)

    2. Regarding your comment about NOW() being inaccurate - my cell needs to have both Date and Time.  Is there any workaround for using NOW() if that's the case, or am I just best off using the code you provided and using NOW()?

     

    V
    Forum|alt.badge.img+3
    • vaultsportshq
    • Author
    • New Participant
    • May 14, 2024

    Another issue I'm experiencing related to this @kuovonne .  It seems as though the formula you provided is triggering when the Start Date is before the time in CST (which is where my computer is located at this time).  I would have thought it would be triggering for either EST because that's what the Start Date cell is set up as, or in GMT, as that's what Airtable reads.

    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • May 16, 2024

    I usually set fields to show local time and display the timezone because that is easiest for me to use. I set fields to show the same timezone when people across different time zones need to know the time in the specific timezone. Note that formula fields that result in date/time values can also be formatted for different timezones and the default might not be what you expect. 

    As for the formula changing values when you do not expect, there are too many variables at play, including whether or not the base was open at the time of change. If the base was closed, NOW() can be off by an hour or more.

    Terms & ConditionsAccessibility statement