Help with Conditional Formatting

Hannahsamd

I have to create a report that has conditional formatting onto it. I am not really sure how to work Smartsheet's conditional formatting stuff. The example I was given to create was: "Can color be added based on calendar logic (0-30 days since xxx, for example)" There is an active sheet that we would use to put the conditional formatting on.

This is the excel sheet that I was able to recreate but this is what I am trying to do

I just need help with what to put in the sheet. I am used to the excel conditional formatting so I just do not really know how to work this.

Kaveri Vipat

Hi Hannahsamd,

I have applied conditional formatting logic by referencing your requirement and you can use the attached image to create the same conditional formatting in Smartsheet.

If you are an admin or owner of the sheet, you can apply conditional formatting

By clicking this symbol of conditional formatting, then clicking in the Add New Rule you can give the condition then the format you want, and the specific column or the entire row as required, and then apply it.

You can go through the below article of Conditional formatting too.

https://help.smartsheet.com/learning-track/level-1-get-started/conditional-formatting

Hope it will work for you.

Paul Newcome

Comparing two columns to each other like this is going to require a helper column with a formula to output some kind of indicator for your conditional formatting such as

=IF([Date Column A]@row - [Date Column B]@row<= 13, "R", IF([Date Column A]@row - [Date Column B]@row<= 20, "Y", "G"))

Then create your conditional formatting based on the output of the formula.

Kaveri Vipat

Hi Hannahsamd,

I have applied conditional formatting logic by referencing your requirement and you can use the attached image to create the same conditional formatting in Smartsheet.

If you are an admin or owner of the sheet, you can apply conditional formatting

By clicking this symbol of conditional formatting, then clicking in the Add New Rule you can give the condition then the format you want, and the specific column or the entire row as required, and then apply it.

You can go through the below article of Conditional formatting too.

https://help.smartsheet.com/learning-track/level-1-get-started/conditional-formatting

Hope it will work for you.

Paul Newcome

I think this may be an example of what you are looking for:

Hannahsamd

@Kaveri Vipat @Paul Newcome I need it to be If "This columns date" is within 30 days of "this columns date" the green

Or I have others that say of "This columns date" is within 14-20 of "this columns date" then yellow

Is this possible?

Paul Newcome

Comparing two columns to each other like this is going to require a helper column with a formula to output some kind of indicator for your conditional formatting such as

=IF([Date Column A]@row - [Date Column B]@row<= 13, "R", IF([Date Column A]@row - [Date Column B]@row<= 20, "Y", "G"))

Then create your conditional formatting based on the output of the formula.

Hannahsamd

@Paul Newcome How do I make the conditional formatting show up on the reports? I think you helped me in an earlier question I had for this project with creating metric sheets to use for reports. I need this conditional formatting to show up on those sheets and then the reports made out of the metric sheets.

Paul Newcome

Once you get the conditional formatting on the sheet, it should automatically push through to any report referencing the sheet.

Hannahsamd

@Paul Newcome For some reason I cannot get the color to show up on my report. I did the same thins yo mentioned above. What can I do to ensure that it will show up?

Ashley McAdoo

Hey @Paul Newcome,

I'm having a conditional formatting issue as well. I have rules set up so that the cell color deepens as the due date gets closer, ending in dark red if the due date is in the past. Everything is working except the formatting thinks 7/31/24 is in the past.

Also, I am getting this message that I have never seen before that says "your rule already includes disabled columns" or something like that. Do you know what that means?

Date is not in the past.JPG

Disabled columns.JPG

This is on a copy of a sheet someone else created, and I am trying to improve the functionality/add some automation.

Thanks,

Ashley

Paul Newcome

For the first one, double check that the value in that cell is actually being stored as a date value.

For the second, are there any conditional formatting rules that are disabled?