Can I please have an amendment to this conditional formatting

[les361800]

I was kindly provided with this formula to turn a cell red when the date was the next day.
The cell however turns back to white when the date is the current date - can it be amended so that it turns red the day before, and stays red when on or past the date?

Thank you

 

[Deek6t8]

I would say this should work for you hopefully =--SUBSTITUTE(D1,".","/")>=WORKDAY(TODAY(),-1),

I assume from this that your dates are incorrectly formatted as dd.mm.yyyy, If I were you I would avoid using dates in that format

 

[awoohaw]

Do you always want that cell to be RED once it has first become RED? Or for a specific number of days, or when another condition overrides it?

Is D1 currently a text string that looks like a date?

 

[les361800]

Do you always want that cell to be RED once it has first become RED? Or for a specific number of days, or when another condition overrides it?

Is D1 currently a text string that looks like a date?

Yes, please see the attached. It's a simple doccument to record jobs and date they need done by. Would just like the date to turn red the day before and stay red.
D is a date format column.

Thanks!

 

[awoohaw]

Yes, please see the attached. It's a simple doccument to record jobs and date they need done by. Would just like the date to turn red the day before and stay red.
D is a date format column.

Thanks!

Then the formula by @Deek6t8 will probably suit your needs. But, eventually all the cells in the column (with future dates) now will be permanently red.
If the conditional formatting is for taking action, why not remove that formatting when the action has been completed?

What happens when you change the format of column D to GENERAL? Does a date serial number appear? Or do you just have the column left justified?

 

[les361800]

I would say this should work for you hopefully =--SUBSTITUTE(D1,".","/")>=WORKDAY(TODAY(),-1),

I assume from this that your dates are incorrectly formatted as dd.mm.yyyy, If I were you I would avoid using dates in that format

Hey, this didn't work

The dates seem to be fine with the original formula?

 

[les361800]

Then the formula by @Deek6t8 will probably suit your needs. But, eventually all the cells in the column (with future dates) now will be permanently red.
If the conditional formatting is for taking action, why not remove that formatting when the action has been completed?

What happens when you change the format of column D to GENERAL? Does a date serial number appear? Or do you just have the column left justified?

Hi, that formula didn't work - any ideas?
We completely delete a line when the job is complete, I just need the date to be red from the date before until we bin it.

When I change it to general the dates just say the exact same, not justified either

 

[Deek6t8]

The dates you have are not actually dates in Excel they are text, the original Substitute formula converted them into dates for teh comparison by replacing the . with / then did the comparison as I said it would be better if the dates were actual dates then the formula would just be =D1>=today()-1 hope that makes sense

 

[awoohaw]

When I change it to general the dates just say the exact same, not justified either

Yes, column D are not dates, just text. Unless you have a very very compelling reason to store dates as text (which means you won't be able to make charts or pivot tables with date structures for one reason) you should always store dates as date serial numbers. How you format the cell with the date value is your preference.


@Deek6t8 takes care of that with the double unary (--) in the formula by coercing the date string into a date value. The Substitution of "/" for "." is because excel may not (if ever) recognize a "." as a date separator for date value coercion (the sequence of y,m, & d may make a difference too).

 

[les361800]

Yes, column D are not dates, just text. Unless you have a very very compelling reason to store dates as text (which means you won't be able to make charts or pivot tables with date structures for one reason) you should always store dates as date serial numbers. How you format the cell with the date value is your preference.


@Deek6t8 takes care of that with the double unary (--) in the formula by coercing the date string into a date value. The Substitution of "/" for "." is because excel may not (if ever) recognize a "." as a date separator for date value coercion (the sequence of y,m, & d may make a difference too).

Eek that didn't make much sense to me I'm afraid!

I have changed the dates and tried the suggested formula, but it still just doesnt work. All of the cells just turn white?