Skip to main content

Referencing another field in formula results in the date from yesterday

  • April 18, 2019
  • 4 replies
  • 83 views
A

I have a date field by name Due Date. Another field by name Formula is a simple formula as follows:-

IF({Due Date} != "", {Due Date}, SET_TIMEZONE(TODAY(), 'America/Los_Angeles'))

Hence, if Due Date is empty, it simply sets the current date.

If Due Date is not empty, it somehow sets a day which is 1 day behind. I’ve no clue why this is happening.
I have verified that the "include same timezone (GMT)… " option is not checked in the Formula field.

Any help is really appreciated. I’m unable to add image/link to the post so can’t share the exact base. :frowning:

Thanks

    This topic has been closed for replies.

    4 replies

    JonathanBowen
    Forum|alt.badge.img+18

    Hi @Abhijeet_Rastogi - your formula works fine for me:

    I used the slightly amended formula of:

    IF({Due Date}, {Due Date}, SET_TIMEZONE(TODAY(), 'America/Los_Angeles'))

    where, on the first argument of the IF you can say “IF {Due Date}” which translates as “if Due Date exists”. But both formula versions work OK for me.

    Take a look here for instructions to share a base - if we can have a look directly at what you are doing someone might be able to help.

    JB

      A
      • Abhijeet_Rastog
      • Author
      • New Participant
      • April 18, 2019

      Hi @Abhijeet_Rastogi - your formula works fine for me:

      I used the slightly amended formula of:

      IF({Due Date}, {Due Date}, SET_TIMEZONE(TODAY(), 'America/Los_Angeles'))

      where, on the first argument of the IF you can say “IF {Due Date}” which translates as “if Due Date exists”. But both formula versions work OK for me.

      Take a look here for instructions to share a base - if we can have a look directly at what you are doing someone might be able to help.

      JB


      Hi @JonathanBowen, probably because I’m a new member, I don’t have permissions to post a link here so I can’t share the base here.

      I tried your formula too, which is definitely cleaner, it still shows me the older date. If it matters, I changed the timezone’s recently but my local time on the laptop is set to Los_Angeles.

      Justin_Barrett
      Forum|alt.badge.img+21

      Hi @JonathanBowen, probably because I’m a new member, I don’t have permissions to post a link here so I can’t share the base here.

      I tried your formula too, which is definitely cleaner, it still shows me the older date. If it matters, I changed the timezone’s recently but my local time on the laptop is set to Los_Angeles.


      You might need to set the timezone for your due date as well. Even though it should just be a straight copy of the reference date, Airtable will sometimes assume a different timezone when referencing it. Try this and see if it works:

      SET_TIMEZONE(IF({Due Date}, {Due Date}, TODAY()), 'America/Los_Angeles')

      When testing this myself, I had to turn the GMT switch on for the formula, otherwise TODAY() was a day off. I swear, of all the features of Airtable, its handling of time and dates can often be the most confusing.

        A
        • Abhijeet_Rastog
        • Author
        • New Participant
        • April 19, 2019

        You might need to set the timezone for your due date as well. Even though it should just be a straight copy of the reference date, Airtable will sometimes assume a different timezone when referencing it. Try this and see if it works:

        SET_TIMEZONE(IF({Due Date}, {Due Date}, TODAY()), 'America/Los_Angeles')

        When testing this myself, I had to turn the GMT switch on for the formula, otherwise TODAY() was a day off. I swear, of all the features of Airtable, its handling of time and dates can often be the most confusing.


        Hi @Justin_Barrett,

        Thanks for suggesting the GMT option. For now, I had to turn it on for this to be working. The SET_TIMEZONE doesn’t work correctly if the GMT property is not set.

        Abhijeet

        Terms & ConditionsAccessibility statement