Help

Show Tomorrow Date

Topic Labels: Dates & Timezones
1704 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Abraham_Bochner
8 - Airtable Astronomer
8 - Airtable Astronomer

Why is this showing me tomorrow’s date?

Screenshot

IF({How Many Days}>6,{Bill Date},IF(AND(Upload=1,{tracking-number}=BLANK()),TODAY(), {Last Update Tracking}))

5 Replies 5

Manually-entered dates will honor your local timezone settings. However, calculated dates (including those created by the TODAY function) often assume the time should be based on GMT. Sometimes you can fix this by tweaking the options in the Formatting tab of the field’s properties. Other times it requires modifying the field to force date items to a specific timezone by wrapping them inside the SET_TIMEZONE function.

Abraham_Bochner
8 - Airtable Astronomer
8 - Airtable Astronomer

This is what I get when I try the “SET_TIMEZONE function”

http://prntscr.com/nyxgvy

And this is my Formula:

IF({How Many Days}>6,{Bill Date},IF(AND(Upload=1,{tracking-number}=BLANK()),99999, SET_TIMEZONE({Last Update Tracking})))

You somehow got the URL and text options reversed in your example image. Clicking your original tries to open a site named “screenshot”. That link should be:

Screenshot

Sorry that I wasn’t more clear. SET_TIMEZONE requires you to tell it which timezone you want, and it must be used in conjunction with DATETIME_FORMAT. More info on the supported timezones can be found here:

To prevent records with no date from displaying an error, you’ll need to wrap the date formatting inside an IF function. Here’s my modified version of your formula with these changes, using my own timezone as an example:

IF(
    {How Many Days}>6,
    {Bill Date},
    IF(
        AND(
            Upload=1,
            {tracking-number}=BLANK()
        ),
        99999,
        IF(
            {Last Update Tracking},
            DATETIME_FORMAT(
                SET_TIMEZONE(
                    {Last Update Tracking},
                    "America/Chicago"
                ), "YYYY-MM-DD"
            )
        )
    )
)
Abraham_Bochner
8 - Airtable Astronomer
8 - Airtable Astronomer

Thanks for your help, it works excellent

Let me please ask you a side note, which app are you using to create this nice IF statements here?

IF Statements Screenshot

I use BBEdit on the Mac, but any plain text editor will do the trick. It just makes visualizing the structure (and making sure the correct number of parentheses are in place) a lot easier.