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.
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.
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.
- Copy the formula down by double-clicking the fill handle.
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:
- Copy the cell with Ctrl + c
- Highlight the other cells
Edit - Paste Special - Formats - OK
Result: You can quickly see at a glance which items are due today.