Trying to conditionally highlight a cell if "Purchase Date" is greater than Today(-1825)

I've tried using the conditional format logic, but any time I try and set a date condition, other than a calendar date or "TODAY" then I get "not a valid date" error message.


I'm trying to set an at-a-glance LCR cell highlight if we exceed 5 years from the purchase date. Sorry to be obtuse, but sure could use a nudge in the right direction. Thanks in advance for any assistance.

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    I do not know of a way to directly conditionally format using the logic "greater than x days in the past". This, however, can be achieved with a bit of trickery. You will need to modify this based on your exact needs, but the basic idea is to set up two conditional format rules. The first will apply the highlight (background) color of your choice to the cell if it is a date. This will obviously apply to all cells in your column. The second format will then apply a white background (or other color depending on how your column is formatted) if the date is within the last 1825 days, effectively overriding the first format and leaving only dates greater than 1825 days in the past highlighted. Make sure to have the rules in the correct order, as rules above override rules below, which is the trick to making this work.


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    I hope you're well and safe!

    To add to Carson's excellent advice/answer.

    • You could add a so-called helper column with a formula that checks a box or similar when it's true and reference that in the Conditional Formatting rule.

    Would that work/help?

    I hope that helps!

    Be safe, and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 08/20/23

    I personally tend to go with a helper checkbox column in these cases. I would suggest something along the lines of

    =IF([Date Column name]@row< TODAY(-1825), 1)


    The above would check a box in any row that is more than 1825 days in the past. You would then build your conditional formatting to highlight any row where this box is checked.


    If you wanted to get even more accurate... 1825 days isn't quite 5 years. It could be either 1 or two days short depending on where the leap years fall. The below will be truly 5 years to the day every day:

    =IF([Date Column name]@row< DATE(YEAR(TODAY()) - 5, MONTH(TODAY()), DAY(TODAY())), 1)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • To all that provided feedback, thank you! The work-around certainly worked and we're up and running. Sincerely appreciate the timely responses - hope you all have a great summer.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Excellent!

    You're more than welcome!

    Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • dizzy_brain
    dizzy_brain ✭✭✭

    I want to do something very similar (highlight row if date is x years in the past) but I don't understand what you all mean by a 'helper checkbox' column? Where exactly do I put the formula:

    =IF([Date Column name]@row< TODAY(-1825), 1)

    And where will this check a box?

    ( I tried to find explanation of 'helper checkbox column' but couldn't)


    Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would insert a new column and set it as a checkbox type. This new checkbox column that you just created is the "helper" column because it's sole purpose is to "help" with certain functionalities. This column is also where you would put the formula.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • dizzy_brain
    dizzy_brain ✭✭✭

    Thanks, I didn't realise that you can type into the cell over the actual "checkbox"!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!