# Re: Countdown or time difference challenge with weeks in the past

Solved
344 0
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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

1 Solution

Accepted Solutions

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

5 Replies 5
13 - Mars

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

5 - Automation Enthusiast

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

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

This should work.

BR,
Mo

5 - Automation Enthusiast

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