Skip to main content
Solved

Countdown or time difference challenge with weeks in the past

  • April 18, 2020
  • 5 replies
  • 42 views
J
Forum|alt.badge.img+4

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

Best answer by Mohamed_Swella1

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 replies

    Mohamed_Swella1
    Forum|alt.badge.img+17

    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

    J
    Forum|alt.badge.img+4
    • Jolomero
    • Author
    • Inspiring
    • April 19, 2020

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

    Mohamed_Swella1
    Forum|alt.badge.img+17

    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

    J
    Forum|alt.badge.img+4
    • Jolomero
    • Author
    • Inspiring
    • April 19, 2020

    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


    :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
    Mohamed_Swella1
    Forum|alt.badge.img+17

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


    Im glad it worked :grinning_face_with_big_eyes:

    J
    Terms & ConditionsAccessibility statement