Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Alerts About Approaching Due Dates

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Alerts About Approaching Due Dates.

Jonathan developed a worksheet that tracks due dates for various departmental documents. He wondered if there was a way for Excel to somehow alert him if the due date for a particular document was approaching.

There are several ways that this can be done in Excel, and you should pick the method that is best for your purposes. The first method is to simply add a column to your worksheet that will be used for the alert. Assuming your due date is in column F, you could place the following type of formula in column G:

=IF(F3<(TODAY()+7),"<<<","")

The formula checks to see if the date in cell F3 is earlier than a week from today. If so, then the formula displays "<<<" in the cell. The effect of this formula is to alert you to any date that is either past or within the next week.

Another approach is to use the conditional formatting capabilities of Excel. Follow these steps:

  1. Select the cells that contain the document due dates.
  2. Choose Conditional Formatting from the Format menu. Excel displays the Conditional Formatting dialog box.
  3. Make sure the first drop-down list is "Cell Value Is." (This should be the default.) (See Figure 1.)
  4. Figure 1. The Conditional Formatting dialog box.

  5. Make sure the second drop-down list is "Less Than."
  6. In the formula area, enter "=TODAY()" (without the quote marks).
  7. Click the Format button. Excel displays the Format Cells dialog box. (See Figure 2.)
  8. Figure 2. The Format Cells dialog box.

  9. Using the Color drop-down list, choose the color red.
  10. Click OK to close the Format Cells dialog box.
  11. Click Add. The Conditional Format dialog box expands to include a second condition.
  12. Make sure the first drop-down list for the second condition is "Cell Value Is." (This should be the default.)
  13. Make sure the second drop-down list is "Less Than."
  14. In the formula area, enter "=TODAY()+7" (without the quote marks).
  15. Click the Format button. Excel displays the Format Cells dialog box.
  16. Using the Color drop-down list, choose the color blue.
  17. Click OK to close the Format Cells dialog box. (See Figure 3.)
  18. Figure 3. The finished Conditional Formatting dialog box.

  19. Click OK to close the Conditional Formatting dialog box.

This is a two-tiered format, and you end up with two levels of alert. If the due date is already past, then it shows up as red. If the due date is today or within the next seven days, then it shows up in blue.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3179) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Alerts About Approaching Due Dates.

Related Tips:

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

 

Comments for this tip:

Barry    30 Jun 2015, 05:51
@Nadia,

What you ask for can be done in Excel VBA but it is a non-trivial task and would have to be closely tailored to your spreadsheet, and possibly what email Client you use.

I've done something similar recently. Using the Windows "Task Scheduler" to open a spreadsheet daily (at 1am), the Workbook_Open macro then runs checking for alerts (this can be multiple alerts, as many as you like). If an alert condition exists then an appropriate email is sent containing the details of the alert(s) (what kind of alert, who is involved, relevant dates, etc). Crucially, the fact an email has been sent is logged so that if the routine is run again further emails are not sent. As I use the spreadsheet during the day I avoid the alert system running again by passing a command line parameter in the task scheduler that the macro picks up to determine whether or not to run the alert routines.

In parallel to all this I use conditional formatting to highlight alert conditions with different colours for different levels of alert e.g. orange for up to 0-30 days overdue, red for 30+ days overdue.

Nadia     29 Jun 2015, 11:31
Hello, I am fairly new to excel but I need to set up two particular alerts on a spreadsheet. I need to set up an e-mail alert if I have not received an evaluation in 60 days and I also need to be notified when my client is reaching their 8 week mark for training. Is there a way that I can have these two alerts in the same workbook?

Ex: I have a 16 week training period in cells c1-r1. Everyone starts the training at different periods so each cell would have a different date. Each person is supposed to have weekly evals and I note the date in the appropriate weekly column and create a hyperlink to the eval report. Can I create an alert if they have been 60 days without an evaluation?

Ex2: Again 16 week training, different sheet in workbook, I need to be notified by email when they are reaching week 8 which would be column J.

David    05 Apr 2015, 03:37
Thanks Barry. I really appreciate it!
Barry    03 Apr 2015, 07:06
@David,

There is an article and code examples on using Gmail with Excel macros

http://www.rondebruin.nl/win/s1/cdo.htm

Hopefully this is solve your problem
Haider    02 Apr 2015, 19:25
hi
i want to create a sheet which will give me daily alerts example if i have tomorrow events its should give me alert in green column in one day advance and when its reach to same date (event date) it should give alert in read column

THANKS YOU SO MUCH FOR YOUR ASSISTANCE
David    02 Apr 2015, 05:25
@Barry - wow, thanks. Unfortunately, I don't use Outlook as I am hooked on Gmail. Nevertheless, thanks for the detailed response.
Barry    31 Mar 2015, 05:38
@David,

Yes you can send an email from Excel but is quite restricted unless your email Client has a VB object model accessible by Excel which generally means that you need to be using Microsoft Outlook as your email client.

In either case the "alert" is a continuing condition whereas sending an email is an individual event, so steps have to be taken to only send the email once, and if required build in any re-sending at prescribed intervals.

Ron de Bruin (Excel MVP) has lots of example VBA code on his website for sending emails: http://www.rondebruin.nl/win/section1.htm

I use the following sub-routine to send an email from MS Outlook adapted from a routine from Ron de Bruin:

Sub SendeMail(strTo As String, strSubject As String, strBody As String, Optional strCC As String, Optional strBCC As String, Optional strAttachment As String)
'Working in Excel 2000-2013
   
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = strTo
        .CC = strCC
        .BCC = strBCC
        .Subject = strSubject
        .Body = strBody & BCSSig
        .Attachments.Add (strAttachment)
        .Send 'or use .Display to display the finished email without sending (useful for debugging)
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

This is then simply called in the main macro using "SendeMail" plus the appropriate parameters (To address, Subject and body text plus optionally any CC, BCC, and/or attachments. E.G.

      SendeMail "Test@Test.com", "This is the Subject", "This is the body text"

Multiple recipients can be emailed by using a ";" as a delimiter between email addresses in the To, CC, or BCC strings.


David Shalev    30 Mar 2015, 12:35
Is there anyway to have Excel trigger an email alert when a particular condition is met? In other words, rather than a conditional format, have Excel email a notification? My guess is no, but thought I would ask!
Thanks!
David
Barry    30 Mar 2015, 05:51
@Ahsan

As you'll be re-sorting data every time something is changed there should be no necessity to re-sort when the file is opened.

If you place the code below on the codepage for the sheet containing your data this will sort the data whenever any data (date) in column C is changed.

To highlight expired entries use one of the methods mentioned in my other comments to this Tip.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("C2"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A1").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Ahsan Shah    30 Mar 2015, 01:53
Hello,
Is There anyway to highlight and sort entire row according to date ?

for Example:

NAME DESIGNATION EXPIRY DATE
123 ABC 26/mar/15
345 ABC 23/MAR/15
678 ABC 20/MAR/15

Now I want to sort them according to expiry dates and want to highlight them Automatically every time i open sheet or present date.
Barry    26 Mar 2015, 06:22
@Clare

What you want is quite easy, assuming your expiry date is in column M

=IF(AND(L3<(TODAY()+14),L3<>"",M3>=TODAY()),"<<<","")

The term "M3>=TODAY()" makes the AND function false if the expiry date is passed, the other term L3<>"" remove the highlight if there is no date in L3.
Clare    25 Mar 2015, 07:23
Hi I am hoping the first =IF(L$3<(TODAY()+14),"<<<","")
will work, can I add an expired date to this also?

Regards
Clare
Barry    24 Mar 2015, 06:07
@Clare,

I do this on several spreadsheets that I use for keeping tabs on unpaid invoices.

First of all, select the range of data that this will be applicable to, then select the top leftmost cell(if you have a header row then this will be cell A2). In the Conditional Formatting dialogue box use the drop down on the "Cell Value is" and select "Formula Is".
Assuming the due date is in column H and the Date Paid is in column I then put the following formula in the formula box:
=AND($H2<Today(),$H2<>"",$I2="")
set the formatting that you want if the due date is past, and click OK.

If the top leftmost cell is say A3 then the formula would be =AND($H3<today(),$H3<>"",$I3="")

The secret is in the terms in the AND function the first ($H2<Today()) determines if the date is past due, the second term ($H2<>"") prevents the highlight if the due date is blank, and the the third term ($I2="") takes the highlight off as soon as you enter the Date Paid into the cell (it actually tests for a blank cell so anything in the cell will remove the highlight).

The "$" signs in the formula allow you to conditionally format the whole row but each cell is still testing the same columns, so the whole row will be lighted not just the Date cells.

In my spreadsheets I have usually a second condition which turns the crow red if the Date Due is 30days overdue (use the term $H2+30<today(). This needs to be the first condition to override any other condition set.
Clare    23 Mar 2015, 08:19
Hi
What I am trying to do is have a column highlighted when a due date is approaching & past. The due date I have is in another cell.

This is a warning to me when I need to chase items that are late or overdue.

I would then like to be able to add my approval date in the column and the cell then not be highlighted.
hoping you can help!

Thanks
Clare
Marc T.    13 Mar 2015, 12:07
Thank you this is exactly what I was looking for.
Marie C.    10 Mar 2015, 14:58
Hi Allen,

As an Excel newbie, I really appreciate your tips and tricks. I was having trouble constructing a due date formula and after carefully following your step by step guide I identified the issue. I am so grateful for you taking the time to share your knowledge and expertise! Sincerely, Marie C.

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 4+5 (To prevent automated submissions and spam.)
 
          Commenting Terms
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2015 Sharon Parq Associates, Inc.