How To Set Conditional Formatting In Excel For Dates

---

MS Excel 2010: Automatically highlight expired dates and dates that are 30 days from expiration

This Excel tutorial explains how to use conditional formatting to automatically highlight expired dates and dates that are 30 days from expiration in Excel 2010 (with screenshots and step-past-step instructions).

Question: In Microsoft Excel 2010, is there a way to automatically highlight upcoming and past due dates?

For instance, I accept dates that certificates volition expire in a spreadsheet. I would like Excel to highlight the ones that are 30 days from expiration in yellow and the ones that are past the expiration in reddish. Is that possible?

Reply: Yes, you can use conditional formatting to achieve exactly what y'all are looking for.

Get-go highlight the range of cells that yous want to use the formatting to. In this case, nosotros've selected all of column A since we don't know how many rows will accept expiration date values.

Select the Dwelling house tab in the toolbar at the pinnacle of the screen. So in the Styles group, click on the Provisional Formatting drop-down and select Manage Rules.

When the Provisional Formatting Rules Director window appears, click on the "New Rule" push to enter the start condition.

When the New Formatting Rule window appears, select Format merely cells that contain as the rule type.

Then select Cell Value in the first drop down, less than in the second drib downwardly, and enter the following formula that uses the NOW function:

=Now()+30

Next, nosotros need to select what formatting to apply when this condition is met. To practise this, click on the Format push button.

When the Format Cells window appears, select the Fill tab. Then select the colour that you lot'd like to see the dates that will expire in the adjacent 30 days. In this case, nosotros've selected yellow. Then click on the OK button.

When you return to the New Formatting Dominion window, you should see the preview of the formatting in the Preview box. In this example, the preview box shows yellowish as the fill color. Next click on the OK button.

This will return you to the Conditional Formatting Rules Manager window.

You will need to click on the New Rule push over again.

When the New Formatting Rule window appears, select Format merely cells that contain as the rule type.

So select Cell Value in the first drop downwards, less than in the second drib down, and enter the following formula that uses the NOW function:

=NOW()

What this formula means is that the date in the cell is by today's appointment. To select what formatting to apply when this condition is met. To exercise this, click on the Format button.

When the Format Cells window appears, select the Fill tab. And so select the colour that y'all'd similar to encounter the expired dates displayed in. In this example, we've selected scarlet. Then click on the OK button.

When you return to the New Formatting Rule window, you should see the preview of the formatting in the Preview box. In this example, the preview box shows crimson every bit the fill color. Next click on the OK button.

This volition render you to the Provisional Formatting Rules Manager window.

You will need to click on the New Rule button over again.

When the New Formatting Rule window appears, select Use a formula to determine which cells to format as the rule blazon.

Then enter the following formula that uses the ISBLANK function:

=ISBLANK(A1)=TRUE

What this formula means is that if whatsoever cells in column A are blank, do not apply the yellow or carmine formatting. The value of A1 is put every bit the parameter in the ISBLANK role since this is the first value in the range of cells that you've selected. Since the formula uses relative referencing each value in column A will exist evaluated individually.

Next, we need to select what formatting to apply when this status is met. To do this, click on the Format button.

When the Format Cells window appears, select the Fill up tab. Then select white every bit the colour that you'd like to see as the make full in the blank cells. Then click on the OK button.

When y'all return to the New Formatting Rule window, yous should see the preview of the formatting in the Preview box. In this example, the preview box shows white as the fill colour. Adjacent click on the OK button.

Now when you lot return to the Provisional Formatting Rules Managing director window, brand sure that you check the "Stop If Truthful" checkbox for the get-go rule. If you do not, the blank cells will show every bit cerise fill up because the second condition will also evaluate every bit Truthful.

Click on the OK push button.

Now when y'all render to the spreadsheet, the provisional formatting volition exist applied.

Since we created this example on 11/19/2010, you lot tin can run across that the value in cell A3 volition expire in the next xxx days, while the value in cells A4 and A5 have already expired.

How To Set Conditional Formatting In Excel For Dates,

Source: https://www.techonthenet.com/excel/questions/cond_format4_2010.php

Posted by: davisvoinficand.blogspot.com

Share this post