Track Maintenance Due Dates with Excel

November 18, 2004

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.