Mark using conditional formatting for dates in Excel

This tutorial about using conditional formatting, to dates highlight of MVP Frédéric Le Guen, pulse for help with the translation from French into English associated with special recognition and thanks to Ken to us.

Date functions in Excel make it is possible, date calculations, such as addition or subtraction, which carry automatic or semi-automatic worksheets. The NOW function, the values based on the current date and time is calculated, is a good example of this.

This functionality make a step further if you mix date functions with conditional formatting, you can create tables automatically displayed the date alerts if a period close to is or distinguishes between types of days, such as weekends and weekdays.

Find conditional formatting for dates, go to

Home > conditional formatting > highlight cell rules > a date come forward.

You can select the following date options, from yesterday to next month:

These 10 date options generate rules based on the current date. When you create rules for other data like (such as greater than a month from the current date), you can create your own new rule.

Below you will find step-by-step instructions for a few of my favorite formats for dates.

When designing an automated calendar you need even colour not weekends. With the conditional formatting tool you can automatically change the colors of the weekends by the format are based on the WEEKDAY function.  Suppose you the date table-have a calendar without conditional formatting:

To change the color of the weekends, open the menu conditional formatting > revision

Select the menu in the next dialog box, the use a formula to determine which cell format.

In the text box values format, this formula is true, enter the following formula day of the week to determine whether the cell is a Saturday (6) or Sunday (7):

=WEEKDAY(B$5,2) > 5

Parameter 2 = means Saturday 6 and Sunday 7 =. This parameter is very useful to test for a weekend.

Note: In this case, you must lock the reference of the line, so the conditional formatting to the other cells in the table properly.

Then fit the format of your state by clicking on the format button and select a fill color (orange in this example).

To do this, click OK, and then open conditional formatting > manage rules

Choose to see this worksheet worksheet rules rather than the default selection. Change in the is valid forarea that corresponds to the first selection when creating rules to expand the entire column.

Now, you see a different color for the weekends. Note: This example displays the results in the Excel Web app.

Enrich to the previous workbook could identify even holidays colours. To do this, you need a column with the holiday, the highlight you want in your workbook (but not necessarily in a sheet of paper). In our example we have U.S. holidays in column AH (in reference to the year in the cell B2).

The menu again conditional formatting > new rule. In this case, we use the COUNTIF formula to calculate the number of holidays in the current month is greater than 1.

 =COUNTIF($AH$4:$AH$16,B$5) > 1

Then choose in the dialog box to manage rules, area B4: AF11. If you want to emphasize the holiday over the weekend, at the top of the list to move the holiday rule.

In the Excel Web app below shows the result in this example. Change the value of the month and year to see, such as the calendar has a different format.

For the case that we want to again change the color of the cells, which is based on our approach on a date, we will work to make for us by using conditional formatting.

We will show in the following example:

Yellow dates between 1 and 2 Monthsorange data between 2 and 3 Monthspurple dates more than 3 months

We create three rules then conditional formatting formula DATEDIF . Each of the three cases the following formulas:

=DATEDIF($B2,$E$2,"m") > 0

=DATEDIF($B2,$E$2,"m") > 1

=DATEDIF($B2,$E$2,"m") > 2

In the Excel Web app, try below are some dates to experiment, to change the result.

Rather than a different color for each period within our time frame set selected, work we with color scales possibility is that our cells color.

First, go in a new column (column E), calculated the difference in the number of days formula and the parameters "yd" in a year again with the DATEDIF.

=DATEDIF($D2,today(),"YD")

Then select "the" conditional formatting > new option format all cells according to their value depending on and select the following options:

Scale = 3 ColorsMinimum = 0 RedMidpoint = 10 YellowMaximum = 30 white

The result is a gradient color gamut with shades of white to red to yellow.  The more closer to 0, red, that it will have closer closer knows more of 10 more yellow and on 30.  In the Excel Web app, try below are some dates to experiment, to change the result.

--Frédéric Le Guen

View the original article here