Back

Message Board

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.