IF formula to act on the difference between today and a field date

jo123456 · ‎May 15, 2023

I'm trying to get this formula to calculate the difference between the date 'today' and the date in the field 'End date', then stay blank if it it's more than 10 days but return 'Yes' if it's 10 days or less. I can't get it to return the right values though. Any suggestions for how I can fix this to get the right result?

IF(DATETIME_DIFF(TODAY(),{End date},'days') >10, " ", "Yes")

kuovonne · ‎May 15, 2023

Is your end date in the future? If so, you are going to get a negative result. You may need to flip the order of the dates around.

Try writing a formula with just DATETIME_DIFF() and experimenting with different input dates (both past and future) until you understand how it works. Then nest it inside the IF().

You blank result also doesn’t look blank to me. It looks like a single space character.

See Solution in Thread

kuovonne · ‎May 15, 2023

Is your end date in the future? If so, you are going to get a negative result. You may need to flip the order of the dates around.

Try writing a formula with just DATETIME_DIFF() and experimenting with different input dates (both past and future) until you understand how it works. Then nest it inside the IF().

You blank result also doesn’t look blank to me. It looks like a single space character.

jo123456 · ‎May 16, 2023

Thank you, I hadn't considered negative results so I've found the answer now.