[Solved] DATEADD error when using IF({name}=BLANK(),'',

[AnnicaT]

Hi all!
I’m relatively new to anything beyond simple in Airtable, but I’m learning through trial and error.
I’ve ran into a problem I can’t solve though (yet) and would greatly appreciate any and all help.

When using the following formula i end up getting error messages.

IF(
{Kontroll intervall}="Varje vecka",
DATEADD({Senast kontrollerad}, '7', 'days'),
	IF({Kontroll intervall}="Varannan vecka",
	DATEADD({Senast kontrollerad}, '14', 'Days'),
		IF({Kontroll intervall}="En gång i månaden",
		DATEADD({Senast kontrollerad}, '30', 'Days'),
			IF({Kontroll intervall}="Varannan månad",
			DATEADD({Senast kontrollerad}, '60', 'Days'),
				IF({Kontroll intervall}="En gång per kvartal",
				DATEADD({Senast kontrollerad}, '120', 'Days'),
					IF({Kontroll intervall}="Ombyggnad, ej aktuell i nuläget",
					DATEADD({Senast kontrollerad}, '60', 'Days'),
						IF({Kontroll intervall}="Ej evenemangshemsida",
						DATEADD({Senast kontrollerad}, '365', 'Days'),
							IF({Kontroll intervall}="Behövs inte kollas av",
							DATEADD({Senast kontrollerad}, '365', 'Days')))))))))

As seen here

So I tried altering as follows

IF(
{Senast kontrollerad}=BLANK(),
'',	
IF({Kontroll intervall}="Varje vecka",
DATEADD({Senast kontrollerad}, '7', 'days'),
	IF({Kontroll intervall}="Varannan vecka",
	DATEADD({Senast kontrollerad}, '14', 'Days'),
		IF({Kontroll intervall}="En gång i månaden",
		DATEADD({Senast kontrollerad}, '30', 'Days'),
			IF({Kontroll intervall}="Varannan månad",
			DATEADD({Senast kontrollerad}, '60', 'Days'),
				IF({Kontroll intervall}="En gång per kvartal",
				DATEADD({Senast kontrollerad}, '120', 'Days'),
					IF({Kontroll intervall}="Ombyggnad, ej aktuell i nuläget",
					DATEADD({Senast kontrollerad}, '60', 'Days'),
						IF({Kontroll intervall}="Ej evenemangshemsida",
						DATEADD({Senast kontrollerad}, '365', 'Days'),
							IF({Kontroll intervall}="Behövs inte kollas av",
							DATEADD({Senast kontrollerad}, '365', 'Days'))))))))))	

with following result

My questions are as follows:

1. How do I fix the second formula so that the date displays correctly?
2. For learning purposes - Why does it give the odd addition to the date with the second formula?

[Jeremy_Oglesby]

IF(
   {Senast kontrollerad},
   SWITCH(
      {Kontroll intervall},
      "Varje vecka", DATEADD({Senast kontrollerad}, 7, 'days'),
      "Varannan vecka", DATEADD({Senast kontrollerad}, 14, 'days'),
      etc... following the pattern
   )
)

make sure your final DATEADD() statement does NOT have a comma after it - just the closing parenthesis

Instead of checking for blank cells and proactively filling this one with an empty string, it’s better to check for the presence of a value in the date field, and only evaluate the rest of the IF() statement if there is a value in it.

The SWITCH() statement can replace a nested IF() statement if you are repeatedly checking the value of the same field over and over, and asking for the evaluation of a different function depending on its value.

The reason your second attempt returned ISO formatted dates is because Airtable recognized that your formula could evaluate to one of TWO data-types - if your date field is blank, you were telling it to evaluate to an empty string - ‘’ -but an empty string is still a string, which is a particular data-type, and is different from a date data-type. To resolve this, Airtable was formatting your dates as strings (the least common denominator data-type) and not allowing you to choose it’s date-output format.

Airtable only allows you to choose a date data-type output format if your formula is only able to return a date. The method I shared still checks for a date in your date field first, to avoid the ERROR#, but instead of returning an empty string when it’s blank, it simply does nothing - this leaves the cell in a state called “null”. It means “this field is reserved to hold a specific data-type (in this case, a date), but currently has no value.”

null =/= ‘’ =/= ERROR#

[AnnicaT]

Thank you so much for your help and extra so for taking the time to explain it all.
That worked perfectly.