Create Date Sensitive Colour Changing Cells In Excel

Fri 23rd September 2011

Billy's DVD rental store is ticking over, but he has a problem with overdue returns slipping through the net unpenalized. A friend sets up his Excel worksheet to display a different coloured cell depending on whether the film is returned on time, late or very late.

Billy has bought a job lot of used DVDs to set up a movie rental business. Instead of renumbering the entire batch of discs and cases, which were ex-rentals, he has used their existing numbers, but he applied Excel's Ensure Unique Values function to avoid duplicating any numbers. He knows his stock, and has unique references so that he can keep track of them. So far, so good for Billy.

However, the DVD rentals are a sideline in his grocery store, so trade is not booming, but it ticks over. This slow but steady stream of custom means that there is no need to buy a specialist application to monitor rentals and returns; Billy can keep track of things in an Excel worksheet. The only data Billy needs to enter is today's date, which he does by pressing Ctrl + :

One area where Billy had problems was with overdue returns. There was nothing on the worksheet to tell him that a rental had been returned late and he had to check each return against its rental date. When the shop was busy, some late returns were slipping through the net. Then a friend showed him how to solve this problem, and he now has an excellent system in place.

When a DVD is returned on time, Billy enters the date in the Returned On column and that cell automatically turns green to indicate no penalty. If the rental is returned between one and three days late, the cell turns yellow. This indicates that the customer is late and a small penalty should be imposed. Any DVDs returned over three days late will trigger a red cell, which means a more stringent penalty. It is all very clear and it works perfectly, but what did Billy's friend do?

She set up the worksheet to perform these actions using our old friend, conditional formatting. Here's how to do it.

Select the column to which you want to apply the formatting. Go to Format and select Conditional Formatting from the list to open the relevant dialog box. For Condition 1, select Cell Value Is, and equal to. In the right hand box enter the following:

=TODAY()

Click on the Format button and select the Patterns tab. Choose a colour (in Billy's case green) and click OK.

Click Add to create Condition 2, and repeat the process, but this time you are entering a slightly different formula. Select Cell Value Is, and in the following box click on the down arrow and select Between. In the first box enter the following:

=TODAY()+1

In the second box enter:

=TODAY()+3

This gives us a range of one to three days after today. Click Format and select a colour as before, then click OK. Click Add to set up Condition 3.

This is slightly different again, as there is no upper limit to this condition. Again Cell Value Is is left in place, but this time from the drop down list, select Greater Than. You only have one formula box again, and into this type the following:

=TODAY()+4
Click OK and the formatting is complete.

In doing this we used three different cell values in the Conditions box: equal to, between and greater than, and the basic formula had to be adapted for each condition. This is a good way to get 'under the bonnet' of Excel to see how different actions affect data. I have learned a great deal simply by playing around with various dialog boxes and experimenting. Dedicated training in the use of this popular application, however, would offer a more professional, streamlined approach to becoming an Excel wizard than my 'suck-it-and-see' method.

Author is a freelance copywriter. For more information on microsoft training london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1967-create-date-sensitive-colour-changing-cells-in-excel.html