Conditional Formatting with due dates

Willc13

Hi -

I'm trying to set up conditional formatting within individual cells. The sheet will have a list of projects and a project completion due date. I'm trying to set up conditional formatting specific to each individual due date. Each project will have it's own row and one of the columns will be the due date column.

Is there a way I can set it up so when a due date gets changed once the date comes and goes. So for example, once two projects have their due dates up, the formatting will change those individual cells with the dates yellow, red, or whatever color.

I've tried doing this but it seems like it wants me to set up the formatting for the entire column. I'm happy to provide more information.

-Will

Mike TV

@Willc13

Make sure your Date Reached column is set to be a checkbox column type.

Mike TV

@Willc13

Is this what you need the sheet to do? BTW, yellow is a poor color to use unless changing the background it's too hard to see.

Example sheet:

Conditional formatting settings:

Sameer Karkhanis

In Smartsheet you cannot format any specific range of cells, the rules are defined for the entire column only.

Willc13

If it is not possible to do conditional formatting for a specific cell, is there a formula I can use for this?

For example, if I wanted to highlight a date once it reached that date, let's say 6/2/2023, is there a formula that I can use that will flag that date once it has come and gone?

I am trying to use this to flag/highlight dates once the date arrives.

Mike TV

@Willc13

Create a column with your target date and fill in the target date for when you want it to change color. Then create a 2nd column that's a checkbox column with an IF formula such as:

=IF([Due Date]@row >= [Target Date]@row, true, false)

Then set up your conditional formatting to color the row or the Due Date when the box is checked in your formula column.

Willc13

Thank you! That worked, but it is highlighting the date even though the date hasn't come yet. How would I keep the cell so it does not change until target/due date is reach. Would I use the =TODAY function in some way? I want to set this up so the conditional formatting changes when the date is reached. This doesn't seem to work because it is highlighting the date even though the date is still in the future.

Mike TV

@Willc13

It may actually be more simple than my first explanation? Does this accomplish what you're needing?

Date Reached formula:

=IF([M&V due date]@row <= TODAY(), true, false)

Willc13

This seems to be what I need! But I am getting an invalid operation error when trying to enter the formula.

When I try to hand type it, it is giving me an "UNPARSEABLE" error.

Mike TV

@Willc13

Make sure your Date Reached column is set to be a checkbox column type.

Willc13

It worked I think!

My date reached column was already set to be a checkbox column type - but when I changed my M&V due date data type to date instead of text, it worked.

Willc13