Hi @Jolomero,

You can use the IF formula for sure. You would use it just before this formula, add <0 so that in case it is negative it returns to you “Complete” then the false statement should be the DateTime_Diff formula again.

BR,


Mo

Thanks for the suggestion, and I’m still doing this incorrectly:


IF(DATETIME_DIFF({IBP Ends}, TODAY()<0,'complete') DATETIME_DIFF({IBP Ends}, TODAY(),'weeks')

When I use:


IF({IBP Ends}<0, TODAY(), "complete") all the records show ‘complete’

When I add-in the rest of the formula:


DATETIME_DIFF({IBP Ends}, TODAY(), ‘weeks’) then I get an error message.

IF(DATETIME_DIFF({IBP Ends}<0, TODAY(), “complete”), DATETIME_DIFF({IBP Ends}, TODAY(), ‘weeks’)


tells me that the formula’s invalid - I just can’t see how.

Here’s another version: IF({IBP Ends}<0, TODAY(), “complete”, DATETIME_DIFF({IBP Ends}, TODAY(), ‘weeks’))


This gives me all records show ‘complete’, AND in trying to parse the formula, all of the DATETIME_DIFF section gets deleted. :woman_shrugging:

Perhaps it’s bedtime, so I don’t end up spiralling down the hole!

Hi @Jolomero,

You almost got it the first time

IF(DATETIME_DIFF({IBP Ends}, TODAY(), 'weeks')<0,"Complete", DATETIME_DIFF({IBP Ends}, TODAY(),'weeks'))

This should work.

BR,


Mo

Yup I tried everything but the right way! Thanks so much for the help and the new lesson in formulas!

My new resolution:


[sic] complex formulae should be written down, marked with colourful highlights and pasted somewhere in your room where it will catch your glimpse regularly. (The Dhaka Tribune)

Cheers!

PS: it worked perfectly!

Im glad it worked :grinning_face_with_big_eyes: