Due Date Column. How can i mak ethe dates turn red 5 days before they are due

Garry Lind

I used a conditional but it seem swhen the due dates comes up within the 5 days it does not turm red using a conditional. It turns red if i manually put the date into the column cell in the 5 day period

Paul Newcome

Are you able to provide a screenshot of your sheet and your conditional formatting rule(s)? Sensitive/Confidential data can be removed, blocked, or replaced with "dummy data" as needed.

Garry Lind

Jeff Reisman

Are you sure the Due Date (Tasks) column is actually a Date column? If not, date formulas used in the Conditional Formatting might not work properly.

Garry Lind

It is a date column

Jeff Reisman

So if you don't enter a date manually, how else does the date get populated?

Or are you saying that if you enter a date that is 6 days out, then tomorrow when it becomes 5 days out it doesn't turn red - unless you re-enter the date manually inside the 5 day window?

Garry Lind

Tomorrow will be my test. I will confirm if the 03/02/20 dates turn red automatically. It seems that they have not turned red before unless i mess with the date manually

Paul Newcome

If the due date is calculated through a formula, it could be output as a text string that simply looks like a date (but doesn't function as one). If your dates don't turn red today, let us know. And we may have some more trouble shooting tips for you.

Garry Lind

I opened my sheet today and todays dates 3/02/20 were still black until I refreshed the sheet. Then they turned red. Maybe that is the only way it will work?

I as hoping it would turn red without having to do anything with the sheet. Is that possible?

Jeff Reisman

Could be a timezone issue. For some things Smartsheet calculates dates using your personal time zone; for others it calculates using Pacific time. For example, I'm in Eastern time, I have conditional formatting that says "turn red when date created < TODAY(-1)". If Date Created = 2/25/20 11:10:23 AM, it turns red when I hit 2/26/20 8:10:23 AM.

If your date columns are not system columns, you could try using a helper column for today's date, with formula =DATEONLY(TODAY()) and use that in your date calculation against your Due Date column in another helper column:

=IF([Due Date]@row >= ([Current Date]@row - 5), 1, 0)

The set conditional formatting to set your red font when that second helper column = 1.

Paul Newcome

They should be turning red even without refreshing the sheet. Did you leave it open overnight?

Garry Lind

I did not the sheet open

Paul Newcome

@Garry Lind That's odd. I would suggest reaching out to SUPPORT to see what they have to say about it. I haven't had any problems with the conditional formatting in any of my sheets, but most of mine is also not date triggered.