Conditional Formatting Between 2 Dates

[johannes2008]

Hello

I am trying to use conditional formatting so that cell A18 will go red if the value id D18 is greater then today's date but D19 is less then today's date. I want to repeat this for cells A18:A28

I have a number of steps in a process that happen weeks apart and the start date for those steps are listed in column D. What I am trying to do is have the cell corresponding with the current step we are in be highlighted so people can easily determine where we are in the process.

I used the below formula but it is not highlighting the correct cell.

=AND(D18>TODAY(),D19<TODAY())

The formatting in column D is "ddd, dd-mmm-yy"

 

[arthurbr]

1. Perhaps =AND($D18>TODAY(),$D19<TODAY())
2. Do you have real dates or text looking like dates ( in the latter case left aligned in the cell when no manual alignment applied)
Cell format is NOT relevant

 

[Monty Norman]

Hello

I am trying to use conditional formatting so that cell A18 will go red if the value id D18 is greater then today's date but D19 is less then today's date. I want to repeat this for cells A18:A28

I have a number of steps in a process that happen weeks apart and the start date for those steps are listed in column D. What I am trying to do is have the cell corresponding with the current step we are in be highlighted so people can easily determine where we are in the process.

I used the below formula but it is not highlighting the correct cell.

=AND(D18>TODAY(),D19<TODAY())

The formatting in column D is "ddd, dd-mmm-yy"

Hello @johannes2008
Use this formula in your conditional formatting to get what you want.
=AND(D18>A1,E18<A1)
In A1 you have the function: =TODAY()

You see, Conditional Formatting (CF) work on a TRUE/FALSE basis. If the condition is TRUE then the CF is applied. Any formula that returns TRUE/FALSE will work for CF. So here we are comparing the date in D18 to today's date and the date in E18 and if both conditions return TRUE bingo the CF is applied. Now since the TODAY() function is volatile, that means it changes at each recalculation of the Range/Worksheet/Workbook thus consuming resources you should use it sparingly.

Now that I gave you a starting point, my question to you is how will you handle the volatility of the TODAY() function? You may need to think about fixing the date so that it will not keep changing each time.

Let us know if you need any help.

Monty