Displaying a formula produced future date based on one existing date and the selected value of another field

[David_Voynow]

I want to use an IF statement in a field where the logic will basically be the following:
IF({Calibration Frequency}= Annual, ({Last Calibrated}+365 days))

I know this isn’t a valid way to code this - strictly an attempt to explain the intent. But basically, the {Calibration Frequency} field is biannual, annual, or 2 years. Depending on that value, i want the field to display the correct “due date” in the coded field based on the calibration frequency and the {Last Calibrated} date.

[Mohamed_Swella1]

Hi @David_Voynow ,

Welcome to Airtable Community !

You are on the right track.

The formula should be as follows:

DateAdd({Last Calibrated},SWITCH({Calibration Frequency},“Annual”,365,“Biannual”,182,“2 Years”,730),“Days”)

Make sure the spelling of the items are correct please.

[David_Voynow]

Thanks @Mohamed_Swellam. I think I incompletely described the base structure though.

So, there are two existing columns that will be referenced, and one new column field which i’m intending to code to produce a “Calibration Due” date. The two existing columns are “Last Calibrated” and “Calibration Frequency”. Both of these columns are prefilled and will remain. The third column is intended to produce the logical date of the next Calibration based on the last date of calibration and the assigned frequency.

I tried the code that you suggested, but it produced an error and i assume it’s because i inaccurately explained the base and the existing structure. Does all of this make sense? Thanks so much for the suggestion so far! Learning a ton about all of the tools available in Airtable already

[Justin_Barrett]

The formula that @Mohamed_Swellam wrote should work. My gut feeling on why it’s not working is related to the field types of the {Last Calibrated} and {Calibration Frequency} fields. As the formula is currently written:

• {Last Calibrated} must either be a date field, or contain a string in a common date format (Airtable’s date functions are smart enough to auto-convert several common date string formats, but not all of them)
• {Calibration Frequency} must return a string. This means the field could be either long or short text, a single-select, or some other type that returns a string.

One of the most common issues is with lookup fields. They might appear to return simple data types—strings, dates, numbers, etc.—but most of the time they actually return an array, which is a container of other simple types. If either of those fields referenced in the formula is a lookup field, the formula will need to be modified to work correctly (or you might be able to change the lookup to another type).

[David_Voynow]

Hi @Justin_Barrett. Thanks for the reply. The {Last Calibrated} field is formatted as a Date field and the {Calibration Frequency} is set up as a single select. No Lookup field is in use. @Mohamed_Swellam 's code is still not working for some reason. Is there a similar coding approach that could be taken since i’m not sure what to even adjust in this case?
Thanks again

[Mohamed_Swella1]

Hi @David_Voynow ,

It would be easier if we can see a screenshot of the fields ?

[Justin_Barrett · Accepted solution]

@David_Voynow One other thing to note is that if you directly copied and pasted the original formula that @Mohamed_Swellam wrote above, you’re going to get errors because of styled quotes. The formula styles all quotes by default, and styled quote don’t work in Airtable formulas. To get unstyled quotes, formulas added in forum comments should be formatted using the preformatted text option (the toolbar icon that looks like this: </>

After spreading it across multiple lines for easier copying, formatting like I just described, and manually fixing the quotes, we get this output, which is safe to copy and paste into Airtable:

DATEADD(
  {Last Calibrated},
  SWITCH(
    {Calibration Frequency},
    "Annual", 365,
    "Biannual",182,
    "2 Years",730
  ),"Days"
)

[David_Voynow]

Thank you Justin! Yes, this works. Thank you both (@Mohamed_Swellam ) for the help here! Learning a lot

[David_Voynow]

@Justin_Barrett @Mohamed_Swellam One last question: If i wanted to further DATETIME FORMAT the output to exclude the time and instead display just the month and year, how would i incorporate that code into this one you’ve provided?

[Justin_Barrett]

You could wrap the entire formula in a DATETIME_FORMAT() function, or you could use the formatting options on the formula field to only show the date. The latter option sounds like it would work best in your case where you actually want to keep the date as a date and not just a text string, which is what DATETIME_FORMAT() would give you.