Conditional Formatting Not Working with Formula: change colour based on date

Patrick Reisch
edited 06/11/20 in Formulas and Functions

Hello everyone,

I'm trying to conditionally change the background color of a date column based on if it is today or yesterday. It works just fine if I type in a date, but if the date is populated via formula then it doesn't work. I have tried disabling and re-enabling with no luck. The cell in question references 2 other cells on the sheet like so:

=IF([Last Action Date]6 <> "No Data", IF([Last Action Date]6 > [Last Clean Date]6, [Last Action Date]6, [Last Clean Date]6), [Last Clean Date]6)

The cells it references reference other sheets, like so:

=IFERROR(INDEX({Tank Actions Date}, MATCH([Action Highest #]12, {Tank Actions #}, 0)), "No Data")

See attached photo for rules (top rule works just fine for a non-date condition)


Any idea why this might not be working?


Thanks

Best Answers

  • Patrick Reisch
    Answer ✓

    Hi everyone,


    Thanks a ton for the input. Based on your comments I surmised that I was in fact populating with a text value into the data type column. This was due to getting the date via a LEFT function on the created by column. I switched to the DATEONLY function and it is now functioning properly.


    Good to know that just because the column was a date type that a text value could still populate it and cause issues like this. I assumed that it just converted to date type automatically when the column type was set to that.


    Thanks again!

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Patrick,

    I've recreated your formulas in my sheets and my Conditional Formatting rules work as expected.

    Could it be that the dates being returned aren't Today or Yesterday? Would you be able to show us a screen capture of both the sheet you are working within and the source sheet for the INDEX(MATCH? (Please block out any sensitive data).

    Thanks!

    Genevieve

  • Hi Genevieve,


    It seems like the dates aren't being recognized as dates. If I type the same date as the formula returns into the cell then the formatting works, so I don't think it's an issue with the date in the cell not being Today/Yesterday.


    Here is the main sheet with the conditional formatting (as well as the formulas from the first post)



    Here are the 2 sheets that 2 INDEX(MATCH) functions reference.


    The IF statement from main sheet looks at the dates from the 2 INDEX functions and outputs whichever was most recent. From there I would like the conditional formatting to apply.


    Thanks for your help!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The dates that are being referenced by the formulas...


    [Last Action Date]

    [Last Clean Date]

    {Tank Actions Date}


    How are THOSE dates being populated? The formulas should run the Conditional Formatting IF the formulas themselves are pulling data from actual dates. Are you using formulas in any of those cells that you are pulling from including the cells in the cross sheet reference?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Genevieve P I thought about that too, but both of the formulas should maintain the source data type when it pulls.


    A direct cell reference as in the IF statement and the INDEX function both maintain that as opposed to a function such as JOIN that will always convert it to a text string.


    That's why I personally skipped the step of checking what the formula is pulling and went straight to checking the source data because the formulas SHOULD be pulling the source data type.


    I am leaning towards either the data in the source cells is a text value in a date column, or the source cells reside in a text/number column with dates manually entered...


    So in addition to checking the source data, we should also check the source columns to ensure they are in fact date type columns.

  • Patrick Reisch
    Answer ✓

    Hi everyone,


    Thanks a ton for the input. Based on your comments I surmised that I was in fact populating with a text value into the data type column. This was due to getting the date via a LEFT function on the created by column. I switched to the DATEONLY function and it is now functioning properly.


    Good to know that just because the column was a date type that a text value could still populate it and cause issues like this. I assumed that it just converted to date type automatically when the column type was set to that.


    Thanks again!

  • Genevieve P.
    Genevieve P. Employee Admin

    Glad you were able to sort it out! Thanks for letting us know. 🙂

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Patrick Reisch

    Glad it's working for you now.


    To expand on your realization, text based functions or those that include delimiters (JOIN/LEFT/MID/RIGHT) will convert any output into text regardless of how it is displayed. You would also convert the output to a text string if you included any quotes in the output of your formula.


    For example:

    =DATE(2020, 1, 1)

    and

    =DATE(2020, 1, 1) + ""

    and

    "01/01/20"


    would all be display exactly the same if they were input into a date type column except that the first is stored and can be used as a date value whereas the second and third are a text string.


    This also works the same for numbers.


    =1

    and

    ="1"


    would both be displayed as the number one, but the first is a numerical value (and can be used as such) and the second is a text string and would have to be converted into a numerical value before it could be used as one.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!