Conditional Formatting Dates in Excel
A tutorial on applying conditional formatting to Excel based upon a date in the past, future or present.
Applying conditional formatting on a date within Excel can make it easy to highlight payments that are past their due date or to highlight upcoming events with colors changing as the event approaches.
Just select the cells containing dates with your left mouse button then click the down arrow on the side of the Conditional Formatting menu item from the Home Tab of Excel. Then hover your mouse button over Highlight Cell Rules and select A Date Occurring.
You can select between Yesterday, Tomorrow, In the Last 7 Days, Last Week, This Week, Next Week, Last Month, This Month or Next Month. You can then select to highlight cells matching that criteria.
After applying formatting to one or more of the options you can apply it again to the same group of cells for more options.
This is helpful if you want to highlight dates from last month in red, this month in yellow and next month in green for example.
There are limits to this method so we’ll use another method and create an example using the New Rule option that will use a date that we enter into a separate cell to serve as a reference.
In Cell A10 we will type a heading of Reference Date (this is optional) and in Cell A11 we will type the date we wish to use as a reference. This can be a static data that doesn’t change until you alter it or it can be a date that changes every time you open the Excel spreadsheet.
We’ll select the cells containing the dates that we want to apply the formatting to then click the down arrow on the side of the Conditional Formatting menu item from the Home Tab of Excel. Then click New Rule. Select the option to ‘Format Only Cells That Contain’.
There is an option on the drop down menu to format ‘Dates Occurring’ but that gives the same options as the ‘A Date Occurring’ option. You should instead select the ‘Cell Value’ option from the drop down list. Select ‘Less Than’ for the cell option. Then click the cell selector to bring up the cell selection window. Click out reference cell in which we entered the date then click the cell selection button again.
Select the Format button and click the Fill tab on the window that opens and choose the color red. Then click OK on that window and click OK again to apple that formatting. Any date earlier than the one we chose to reference will now appear in red.
Follow the same procedure again with the same cells highlighted but this time choose the ‘Greater Than’ option and choose the cell with the reference date and format in green.
Follow the same procedure once more with the same cells highlighted but this time choose the ‘Equal To’ option and choose the cell with the reference date and format in yellow.
After these procedures any date occurring before the reference cell will appear read, any date occurring after the reference cell will appear green and any date matching the reference cell will appear yellow. You can adjust the formatting to suit your own needs.
See the example below:
Notice that this only formats the dates themselves and not the entire row of cells in the table. If you wanted to format the entire row you would need to use Conditional Formatting with IF Statements. You will also need IF statements if you want to exclude the rows in which the bill has already been paid in full.