Skip to main content

Hi, I am looking for help as i am struggling to work out why a nested formula will built isn’t working for one login but not the other within our tea, despite it being the same and all the tags, fields etc being mirrored.


We are trying to categorise our database into - Gold, Silver, Bronze and have used the formula below which works for my analyst, but not for me. Can anyone help me understand why it isn’t working on mine


IF(Category = “Gold”, IF(DATETIME_DIFF(TODAY(),{Last Catchup},‘days’)>60,“Overdue”), IF(Category = “Silver”, IF(DATETIME_DIFF(TODAY(),{Last Catchup},‘days’)>90,“Overdue”), IF(Category = “Platinum”, IF(DATETIME_DIFF(TODAY(),{Last Catchup},‘days’)>30,“Overdue”))))


Note: We copied and past the forumla across, so not sure if that has had an impact

Welcome to the community, @Daniel_Gavel!


My guess is that you might have the time enabled for your “Last Catchup” date field, in which case it’s probably a time zone issue for your “Last Catchup” field. I think you can solve the problem by disabling the time.


But if you keep the time enabled, you’ll likely want to make sure that the time zone is evaluated as the exact same time zone for everyone, no matter where they’re logging in from. To do that, you’ll need to change that “Last Catchup” field to be in the GMT time zone (which is on the formatting tab of the field customization screen for that field). If you don’t choose that option, the time zone gets evaluated fo everybody’s local time zone.


However, after you enable GMT, then you’ll have another problem, because the TODAY() function in your formula is only going to evaluate as your local time zone (which is probably not GMT time zone)… unless you set it to a certain time zone using the SET_TIMEZONE function.


For a deeper dive into time zones, you can check out my sample base and training video located here:


Welcome to the community, @Daniel_Gavel!


My guess is that you might have the time enabled for your “Last Catchup” date field, in which case it’s probably a time zone issue for your “Last Catchup” field. I think you can solve the problem by disabling the time.


But if you keep the time enabled, you’ll likely want to make sure that the time zone is evaluated as the exact same time zone for everyone, no matter where they’re logging in from. To do that, you’ll need to change that “Last Catchup” field to be in the GMT time zone (which is on the formatting tab of the field customization screen for that field). If you don’t choose that option, the time zone gets evaluated fo everybody’s local time zone.


However, after you enable GMT, then you’ll have another problem, because the TODAY() function in your formula is only going to evaluate as your local time zone (which is probably not GMT time zone)… unless you set it to a certain time zone using the SET_TIMEZONE function.


For a deeper dive into time zones, you can check out my sample base and training video located here:




Thanks very much for the follow up. I have re-checked and both myself and my team member have everything set-up exactly the same. As such the explanation above doesn’t really work as it would mean completely reworking the formula, which works for them

Thanks very much for the follow up. I have re-checked and both myself and my team member have everything set-up exactly the same. As such the explanation above doesn’t really work as it would mean completely reworking the formula, which works for them


Like I said above, it sounds like a time zone issue, so you’ll find the answer to your problems there. As I previously mentioned above, turn on the GMT setting and/or turn off the time feature.

Time is not enabled and has not ever been. Neither of us has it turned on.


Unfortunately your suggestion does not fix the issue, appreciate the attempt though

Welcome to the Airtable community!


Can you provide more information on the problem and some screen captures?


For example, does the formula refuse to save at all? If so, you might have a problem with curly quotes versus straight quotes or typos in field names.


Does the formula save correctly, but always produce the wrong output? If so, you might have some hidden differences in your field types.


Does the formula sometimes produce the correct output and sometimes produce the wrong output? If so, you might have some differences in your data.


You can also try using a different formula with a SWITCH statement.


IF(

  DATETIME_DIFF(TODAY(), {Last Catchup}, 'days') >

  SWITCH({category},

    "Silver", 90,

    "Gold", 60,

    "Platinum", 30

  ),

  "Overdue"

)

Reply


Terms & Conditions