Countdown or time difference challenge with weeks in the past

[Jolomero]

I’ve been having tons of fun learning how to handle DATETIME and IF() and SWITCH(), but I can’t figure this one out. I am measuring the difference in weeks between two calculated dates, and that bit is OK, but I want to replace all of the weeks in the past with an emoji. Picture added for the 1000 words I could otherwise use…!

I’ve tried using an IF statement but can’t quite figure out which part of the existing statement is IF-able! Any pointers on that or should I be looking at another function for the formula?
Cheers and thanks

[Mohamed_Swella1]

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

[Jolomero]

Mohamed_Swellam:

just before this formula, add <0

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 :rabbit: hole!

[Mohamed_Swella1 · Accepted solution]

Hi @Jolomero,

You almost got it the first time :slightly_smiling_face:

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

This should work.

BR,
Mo

[Jolomero]

Mohamed_Swellam:

You almost got it the first time :slightly_smiling_face:

:laughing: 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!

[Mohamed_Swella1]

Im glad it worked :grinning_face_with_big_eyes: