Countdown or time difference challenge with weeks in the past

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

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.


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!

Hi @Jolomero,

You almost got it the first time :slight_smile:

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

This should work.


: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)


PS: it worked perfectly!

1 Like

Im glad it worked :smiley:

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.