Dates & Conditional Formatting

Okay, its been a few days since my last post..  Its Christmas holidays, but no excuses, so going to add a quick post to cover the scenario of having to maintain a fleet of vehicles.  Lets say they have to be serviced every month or 5000 miles, which ever occurs first.  I will split this into two sections, the first will cover the simple Date function and the second will give an overview of conditional formatting..
Once again, this post will just show the very basics, play about with your own data and ideas to discover more.

Section 1: Adding today's date to your sheet when it opens.

• Open & format your new spreadsheet, pick a cell that you want to display the current date in and type =TODAY()    
• When the spreadsheet is opened the selected cell will display the current date, as per your system clock.  This is seen below, where the cell B1 has =TODAY() inserted and displays the date.

So now we have the current date, we will add our data, I will just use the basic details for five vehicles as shown in the data set below..  With only five vehicles its pretty easy to see which vehicle needs the next service going by date alone.  Its a bit more difficult to identify which vehicle is coming close to doing 5000 miles since the last service and would be extremely difficult to arrange timely services for over 100 vehicles..

Section 2: Conditional Formatting based on cell values.

• We will use this data set for simplicity:

• Note: the current date on the top left and the date of last service in the right column..  We also have the current miles and last serviced miles recorded, so we can easily workout when the next service is due..  But, why not let Excel do the working out???  So by adding a column for how many miles to the next service and a column for how many days to the 4 week service is due (if the miles are low and we are going to service on days rather than miles).  
• To workout the miles left, add 5000 to the last service and takeaway the current miles.  Use this formula:  =SUM((I3+5000)-F3)   where I3 holds the recorded miles of the last service and F3 holds the current miles on the vehile.  Note that if the vehicle has done more than 5000 miles since the last service it will show a negative number.  This is a true representation as can be read as -500 miles or 500 miles overdue...
• To workout the days left, add 28 (for 4 weeks) to the date of the last service and takeaway the current date. Use:  =SUM((J3+28)-B1)   You could use  =SUM((J3+28)-TODAY())   to get the date on the fly, without having to reference the cell B1...
• So here is the updated data set, as you can see it is a lot more User friendly in that we can see that the second vehicle is 1000 miles overdue its service and the last vehicle is due a service tomorrow. 

• So, easy as that is now to see when services are due, we will add a bit of formatting to have the info stand out even more, again if working with over 100 vehicles, services could be missed just using this system ..

• First we will check the miles and highlight as the vehicle gets close to 5000 miles from last service.  I will have 2 scales of when to alert the User, the first will be at say 500 miles left to the service.  This will inform the User that the service is due soon and should be booked etc.. The second alert will be at 200 miles, meaning it is time to get the vehicle serviced.   You can add as many alerts and at whatever miles you want.  I will also add an overdue highlight that will standout as the vehicle must get serviced asap..
• To do this, highlight the cells containing the miles to the next service, click on Conditional Formatting & select New Rule, then from the new formatting rule, select format only cells that contain.  

• As you can see, I have selected Cell Value, set it to less than or equal to, and set the value to apply the conditional formatting at 500.  Now set whatever formatting you want by clicking on the Format button.  
• Now to set the 200 mile alert, just repeat the steps above (as for the 500 mile alert) but this time enter 200..  But make this one stand out a little more than the last, time is getting critical...
• Now lets add a brazing highlight to show that a vehicle is overdue it service..  Again, you can add more levels here, as it may not be a real big deal if the vehicle is a couple of hundred miles over, but if 500 miles over it may break the rules of contract and should not be used..  So, again repeat the steps above and make it stand out..  See below:

• As mentioned, the same can be done with the dates, why not play about with it yourself and try it...  Try things like highlighting the whole row and adding more alerts.
• Note:  A good way to ensure you don't miss any is to carry out quick a sort on the column for miles to next service, this will show the vehicles ordered by when they are due their next service.  If you have vehicles allocated to teams or in order, just cancel the sort when you have the info you want..

www.briancunningham.co.uk
keep learning