Skip to main content

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.

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.


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. 😛

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. 😛


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

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”

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


Remove , "1" from the formula.

Remove , "1" from the formula.


ty, you are my hero! <3

Reply


Terms & Conditions