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

Patrick Reisch

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

Genevieve P.

Hmm I wonder if we can check just to make sure the formula is returning a Date and not just text somehow... could you add in a Helper column just to test, and try this formula:

=IF(ISDATE([Action Date]@row), "Yes", "No")

I agree with Paul, it might have something to do with how the other cells are populated, too.

It worked for me with just referencing the one other formula you had:

Patrick Reisch

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.

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

Patrick Reisch

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

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?

Genevieve P.

Hmm I wonder if we can check just to make sure the formula is returning a Date and not just text somehow... could you add in a Helper column just to test, and try this formula:

=IF(ISDATE([Action Date]@row), "Yes", "No")

I agree with Paul, it might have something to do with how the other cells are populated, too.

It worked for me with just referencing the one other formula you had:

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

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.

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

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.