Conditional Formatting date with red Amber or Green

This post will guide you how to highlight the date with different color using the conditional formatting feature in excel. How do I conditional formatting date with red, amber, or green color in excel. How to highlight the date with red if the cell dates is past now(). How to highlight the date with amber if the cell date is past now but within the next 6 months from now(). How to highlight the date with green if the cell date is more than 6 months from now.

Conditional Formatting date with red Amber or Green


If you want to conditional formatting a list of date with different colors in excel, you just need to do the following steps:

#1 select the date list in your worksheet

conditional formatting date1

#2 go to HOME tab, click conditional Formatting command under Styles group. Then click New Rule…from the drop down list. The New Formatting Rule dialog will appear.

conditional formatting date2

#3 click Use a formula to determine which cells to format in the section list of the Select a Rule Type. Then type this formula into the Format values where this formula is true text box. =B1<TODAY()

conditional formatting date3

#4 click Format button, the Format Cells dialog will appear. Switch to Fill tab, select the red color.

conditional formatting date4

#5 click OK button. You will see that the past dates in the selected range are highlighted with red color.

#6 To highlight the dates within the next 6 months from now, just use this formula =AND(B1>=TODAY(),B1<EDATE(TODAY(),6)) in the above step 3, then repeat the steps 4 and 5. Choose the Amber color in format Cells dialog.

conditional formatting date5

#7 to highlight the dates more than 6 months from now, just use this formula: =B1>EDATE(TODAY(),6) into the Format values where this formula is true text box in the above step 3. Then repeat the steps 4 and 5. Choose the green color in Format Cells dialog.

conditional formatting date6

#8 Lets see the result:

conditional formatting date7

Related Functions


  • Excel TODAY function
    The Excel TODAY function returns the serial number of the current date. So you can get the current system date from the TODAY function. The syntax of the TODAY function is as below:=TODAY()…
  • Excel EDATE function
    TThe Excel EDATE function returns the serial number that represents the date that is a specified number of months before or after a specified date.The syntax of the EDATE function is as below:=EDATE (start_date, months)…
  • Excel AND function
    The Excel AND function returns TRUE if all of arguments are TRUE, and it returns FALSE if any of arguments are FALSE.The syntax of the AND function is as below:= AND (condition1,[condition2],…)…
Related Posts
If Cell is This Value or That Value

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of logical test. If you want to see if a cell is A or B, and if one of ...

If Value is Greater Than A Certain Value
If Value is Greater Than A Certain Value 1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the logical test result. If you want to see if a value in one cell is greater than a specific value, ...

If Cell is Not Blank
If Cell is Not Blank 6

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

VBA Macro For VLOOKUP From Another Sheet
vba macro for vlookup from another sheet1

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you how to use VBA code to vlookup data ...

If Cell is Blank
If Cell is Blank_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell Equals Certain Text String
If cell equals certain text_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if cell equals a certain text string like “Win”, you ...

If Cell Contains Either Text1 or Text2
If cell contains text1 or text2_1

IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to see if cell contains certain substring1 like “abc” or substring2 like “def”, and returns true ...

If Cell Contains Certain Text OR Equals Certain Text

IF cell equals certain text IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to test values to see if they equal certain text like ...

VLOOKUP From Another Sheet Not Working
vlookup from another sheet not working3

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working ...

If Cell Begins with One of Three Supplied Characters
If Cell Begins with One of Three Supplied Characters

If you want to test values to see if they begin with some given specific characters like “x”, ”y”, or “z”, you can create a formula with COUNTIF and SUM functions to return results. EXAMPLE You can see “TRUE” or ...

Sidebar