IF / THEN formula with dates - can't edit formula

[Venture_Music]

I’ve seen versions of this post, but not this specific issue, and I can’t ever get Airtable support to answer my help requests so putting it out there to see if anyone has a fix!

In the “Next Renewal Date Calc” field, I want to add 90 days to the adjacent date in the “Current Period Start Date” field, but only if there is data in that record. Otherwise, I’d like the “Next Renewal Date Calc” field to be blank.

However - it appears I’ve got one of two options.

1. Don’t do an if / then statement, just do a date add formula in Next Renewal Date Calc, and then it shows an error in the records that don’t have an adjacent Current Period Start. However, it shows the date formatted for human eyeballs.

2. Write an if / then statement, which returns data as expected, leaving dates only in the fields with an adjacent Current Period Start date. However, it changes the date to a very unfriendly format, and does not allow me to change the format (“Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.”).

I’ve tried changing my if/then formula to a variety of things instead of “” for if the Current Period Start is blank, to see if that helps … tried 0, “-”, MMDDYY … nothing works.

Any ideas?

Thanks!

Option 1 Formula:

Option 1 Results:

Option 2 Formula:

Option 2 Results:

[ScottWorld · Accepted solution]

For your 2nd formula, get rid of the final comma & quotation marks.

[Daniel_Raymond]

Hi, just wondering if you can help me out with this one. I’m using the following formula in my table:

IF({Created time}={Last modified time},BLANK(),IF({Travel Updated}>{Last modified time},“ :thumbs_up: ”,IF({Last modified time}=BLANK(),BLANK(),“ :warning: ”)))

But unfortunately, when {Last modified time} is a few minutes later than {Travel Updated}, is still shows the “ :thumbs_up: ” instead of the “ :warning: ”. Do you have any suggestions on why can do to resolve this?

[ScottWorld]

I just tested your formula, and it works fine for me. Maybe check the time zone (GMT) option on your fields, to make sure that they’re all set to the same option.

[Daniel_Raymond]

Thanks! But how can I check the time zone (GMT) option on my fields?

[ScottWorld]

Customize your time fields, and you’ll see the checkbox at the bottom.

[Daniel_Raymond]

Okay. But which time zone will it match? My windows time zone? Or does airtable have a time zone configuration option?

[ScottWorld]

For a deep dive into time zones, check out my training video & sample base here:

Airtable

Time Zones: Working with multiple different time zones in Airtable - Airtable...

This Airtable base solves the problem of events taking place in different time zones, and users wanting to know what time those events are taking place in...

[Daniel_Raymond]

Great! Thanks a lot!