Sunday, July 19, 2009

Track Maintenance Due Dates with Excel

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.

No comments:

Post a Comment