98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article Excel articles
Create Date Sensitive Colour Changing Cells In Excel
Fri 23rd September 2011
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
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsNational Physical Laboratory
Project Officer J Smillie Access Intermediate This was another great course - Sven is fantastic - very helpful, enthusiastic and knowledgeable. Improvements - perhaps circulate questionnaire in advance to ascertain experience level of clients to check they are on the right course level. I think I probably booked myself onto the incorrect course. Potter Raper Partnership
Secretary Paula Newland Word Advanced Richard's training was excellent. He was very patient and approachable and wanted to help. Just sad that the timescale was against us as wanted to learn more about track changes and felt that we didn't have time (this wasn't Richard's fault as he was brilliant!) Queen Mary University
Molecular Biology Tech Elena Rostkova Excel Intermediate We've learned a lot of very practical and useful thing. Practically everything I will be able to use in my work. Plus this course gave us very good base to investigate other options |
PUBLICATION GUIDELINES