Back

About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store
Podcast
Search
Media
Contact
Home

  Bookmark on del.icio.us!

 

Track Maintenance Due Dates with Excel

 


Amber MacArthur, Bill Jelen, Leo Laporte, Andy Walker on the set of Call for Help on TechTV Canada.

At work, we all have deadlines to meet.

Here is an Excel worksheet with all of the scheduled tasks that have to be completed at a department. The worksheet lists a piece of equipment, the maintenance to be performed, how often it should be performed, and the next due date.

1. It is possible to add a worksheet function to the top of the worksheet that will calculate the current day. Enter the =TODAY() function at the top of the worksheet. This will calculate to show the current day each time the worksheet is opened.

2. Next, you can add a column to the database that subtracts Today from the Due Date in order to figure out how many days away the due date is. Important concept: be sure to hit the F4 key when you point to the Today function.

3. Copy the formula down by double-clicking the fill handle.

4. Cool Trick: Set up a Conditional Format that will use three colors:
  • Tasks Due today in Green
  • Tasks Due tomorrow in Yellow
  • Tasks overdue in Red

    Once the format is set up in one cell, follow these steps: 5. Copy the cell with Ctrl+c

    6. Highlight the other cells

    7. Edit - Paste Special - Formats - OK

    Result: You can quickly see at a glance which items are due today.

    For the BEST TV show on technology, check out Call for Help.

    This tip was originally published on November 18, 2004. The permanent URL for this page is http://www.mrexcel.com/tip082.shtml.

    If you are looking for show notes from another episode, visit my complete list of TechTV appearances.

    MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

    MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

     

Excel is a registered trademark of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

All contents Copyright 1998-2008 by MrExcel Consulting.