IF; Then formula help to generate date

Hey all, I am trying to generate a date in a formula field by using the data from two other fields.

-I need the formula to read the datum in the {End Date (Current)} field, determine if it’s blank and then if it is not. want it to then calculate the date based off ANOTHER field {Days Notice to Cancel}. And if it is blank I want it to say “1”.

So far I feel like the below formulas need to communicate but I am not sure how to combine them to make them talk properly. (however, any suggestions are helpful, even a whole other formula combination).

IF({End Date (Current)}, “field is not empty”
DATEADD({End Date (Current)},-{Days Notice to Cancel},‘days’)
IF({End Date (Current)}, “field is empty”, “1”

TYIA!

The proper format would be:

IF(
   {End Date (Current)}, 
   DATEADD({End Date (Current)}, -{Days Notice to Cancel}, 'days'),
   "1"
)

However, I don’t advise mixing type-outputs for one field. If I read your question correctly, the formula returns either a DATE or the STRING “1”. Ideally, it would always return a DATE or it would always return a STRING. Because there’ll be mixed types, Airtable will read the entire field as STRING which might not be what you want.

1 Like

Thank you! that worked except I think you’re right, it’s now showing as a full date with time which is too much
information. :stuck_out_tongue:

So the question is: what is more useful for your use case, the dates or “1”?

the dates for sure, I would be happy with it being blank, RN it says “error”

Remove , "1" from the formula.

1 Like

ty, you are my hero! <3

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.