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.

Alerts About Approaching Due Dates

by Allen Wyatt
(last updated January 10, 2015)

47

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Avoiding a Section Break Booby Trap

Section breaks got your document formatting all messed up? It could be because of the way you added the section breaks in the ...

Discover More

Examining Styles and Macros in a Template

Templates are very powerful with the ability to contain both styles and macros. If you want to see what styles and macros are ...

Discover More

Printing Only Selected Pages

I often need to print only select pages of a document, rather than the whole thing. Word makes it easy to be judicious in ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (menu)

Forcing Dates Forward

Want to push a date to some pre-defined day of the month? Here's some ways to force the issue.

Discover More

ISO Week Numbers in Excel

Work in an industry that uses ISO standards when it comes to working with dates? You'll love the formula in this tip which ...

Discover More

Calculating Weekend Dates

Do you look forward to the weekend? Well, you can use Excel to let you know when the next weekend begins. Here's how you can ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 4?

2017-04-04 10:26:32

Satish Singh

Hi Sir,
I want to send my due date reminders to google accounts. I have created a program in VBA for sending the reminders but not able set the mail recepient to google mail. kindly help


2017-02-09 06:07:20

Barry

@Dy

You do not specify what kind of alert you want. This tip and the corresponding Tip for Excel 2007,2010,2013 plus their Comments suggest various ways of essentially using Conditional Formatting to highlight entries on a spreadsheet.

Anything else will require some macros to be written, and you'll need to decide how often alerts of whatever type are going to be issued (see my comments on 7 Sep 2016 below).

I have several spreadsheets and I use multiple types of alert. I have pop-up boxes when a workbook is opened to alert me, I use conditional formatting to highlight specific entries (even have the entry flashing - search this site for Flashing Cells for more info on this), I have email alerts for spreadsheets that I only open infrequently or only when there's an alert issued.

It all depends upon what you want.


2017-02-08 06:45:55

Swamy

Thanks a lot it helps me lot request you to pls update advanced tips to to my mail ID


2017-02-06 00:34:43

Dy

I am wanting to set up notifications/alerts from staff's commencement dates for various reasons.


2017-01-31 05:41:59

Barry

@ Terry, Mukesk, Sagari, Rohit, Leendert

I know of no easy way of doing this in Excel, after all it's a spreadsheet program.

Off the top of my head if I wanted to create these types of notification (other than using some more bespoke software solution), I would get Excel to create Calendar entries in MS Outlook and let Outlook issue the required notifications.

I use Excel to send me reminders, via email, of upcoming events/actions. This I do by scheduling Excel to open and run certain workbooks containing the macros at prescribed times (Excel has to be running for any macro to be effective, and Excel as a rule isn't always open). My post on 7-Sept-2016 some of the other issues that have to be dealt with in putting such a solution together.


2017-01-30 09:28:07

Terry Wright

I am looking to set DESKTOP reminders on due dates in my Excel spreadsheets


2017-01-25 07:07:03

Mukesh

Data of 100 customers and i want an alert / pop up reminder in mobile for each customer before due date. Is that possible from excel sheet to mobile. Pl suggest.


2017-01-17 23:37:10

sagari

hi,

i want to make a popup notification about a due date task, how to make them if we have this data

exp
first task taken at 01-17-2017
due date let say 7 days

and if today is 3 day before due date and then a pop up reminder appear when open this excel document for the first time.

thanks before


2017-01-10 02:23:44

Rohit

I 'm looking for how to create notification at desktop (in task bar) activated by due date in Excel 2016 file.


2016-12-31 10:23:24

Leendert

I 'm looking for how to create notification at desktop (in task bar) activated by due date in Excel 2016 file. Thanks in advance. Leendert


2016-09-08 22:28:12

LOVE KUMAR

Kindly notify me about this tip only


2016-09-07 05:14:28

Barry

@Dr. Schick,

To send emails you will have to resort to macros/VBA, this not difficult but the actual macro depends on the email client you have/want to use.

However, this is only part of the solution. You then have to decide how often you want emails sent e.g. every time the spreadsheet is opened? once only?, weekly?, daily?, hourly?. And then what action stops them being sent.

You have to consider what happens if no one opens the spreadsheet/runs the macro for a few days?

I have several spreadsheets which use a dedicated Gmail account to sent out emails automatically. I use the Windows task scheduler to automatically run them at night some on a daily basis others alternate days, and another fortnightly. The Task Scheduler is configured to determine the frequency. After running the spreadsheet it then closes itself automatically. I use command line switches to indicate to the macro whether it is just me opening the spreadsheet to work on it or whether it is a scheduled running of the sheet. Even this is not perfect as the macro won't run if the particular spreadsheet is already open at the time (I've a solution to this but as yet not implemented).

Some elements like the actual sending of emails is generic, but much of the rest of the current code is customised to the applications themselves. My new solution when I get round to coding it will be a bit more generic.

AND of course if the computer this resides on is powered off then nothing is going to happen.

As you'll gather it's a non-trivial task, another contributor to this forum suggests an app called AlladinPRO (http://aladdinpro.com) but I've not looked at this myself but it is a paid for service.


2016-09-06 14:03:48

Dr. Schick

Hi,

Is it possible to set up a customer reminder function that automatically emails customers a reminder?


2016-07-21 12:38:23

Anbarasu T

This command is helpful for me


2016-05-21 06:24:50

Amr El-Atroush

Excellent approche. Can you add a printing macro which selected a rows reached the target date only ? In case of yes is this essential to be run inside the open sheet only ?
Best Regards


2016-05-20 16:26:52

Paola

I followed the steps and my Conditional Formatting dialog box looks exactly as the one pictured. But my second rule (blue) is not showing. Only the first one (red). I have excel 2016 - could that be the issue?


2016-05-04 00:06:30

Saw Win Cho Lynn

Dear Sir,

I would like to create due date alert, for instance,
joining date: July 23, 2015
End date: July 22, 2016

So please could you let me know the formula how to create, when the end date approaching to the end date.

Many thanks


2016-04-13 07:44:45

Sandip Sasturkar

usefull


2016-04-12 11:32:25

Nilesh

Hi,
Your tip is very useful, which helped me. But i have a query, if i hv a data of 100 customers and i want an alert / pop up reminder for each customer after a month, is that possible in excel. pl suggest.


2016-04-12 04:29:14

shiva

Thank you verymuch


2016-04-08 06:59:59

Suren

Good Job

Very useful in my work sheet.

RGDS

Suren


2016-04-07 11:49:42

Gary J.

Great article! I had this same problem of handling too many document renewals. Just found out this app on Google called http://aladdinpro.com and it does the job with email reminders.


2015-12-31 05:45:46

TT

I really appreciate this. i had been beating myself up, as to how would i easily and readily be alert when the audit date for my suppliers is approaching. Thank you sooo much, u are awesome.


2015-12-15 07:21:45

JB

This is helpful however I am also looking for monthly due dates alert,
i have receivables from my clients by month and I got to many clients and I wanted to at least give me some alerts every specific date on the month of the given date of per client.
I hope theres an option for this such thing.
its like putting on your device calendar alarm and asking the repetition of the alert if monthly,quarterly,semiannual or annually. maybe it can also be done in excel..

Thank you


2015-10-12 04:40:16

Claire

I need to construct a formula which will indicate when an item is due to expire


2015-08-05 09:23:21

Rajeev

Hi Allen

I want reminder alerts of Expiry dates of Govt Docs/License renewal/Passport renewal dates/Visa Renewal dates etc which I have already added in my excel sheet.

Kindly Suggest.

Regards

Rajeev


2015-07-29 06:20:31

Janeth

Even though I am still learning the ropes I am finding this very useful. Thank you soooo much


2015-07-28 05:26:14

Barry

@Sam @ Greg

See my comments in the parallel thread on this topic for Excel 2007+ on 23 July 2015

http://excelribbon.tips.net/T009327_Alerts_About_Approaching_Due_Dates.html or click on the link at the bottom of the tip and above the comments.

In the Conditional Format Dialogue box from the "Cell value is " drop down box select "Formula is" and paste in the formula given.


2015-07-27 12:22:27

Sam

Hello

I need some big help ...
I need a formula that can alert or be highlighted in red 30 days after the customer has been inputted in excel ? any thoughts ?
i have date inputted and follow up date colums


2015-07-16 10:54:15

Greg

I need it to highlight the entire row btw


2015-07-16 10:52:49

Greg

I have a shared spreadsheet for insurance purposes and it has clients and effective dates. I need to have an alert for when it is 5 months prior to the date, 1 month prior to date and if it is within the current week.


Having a little trouble understanding the help up top can someone help me out?

thank you


2015-06-30 05:51:05

Barry

@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.


2015-06-29 11:31:52

Nadia

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.


2015-04-05 03:37:40

David

Thanks Barry. I really appreciate it!


2015-04-03 07:06:00

Barry

@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


2015-04-02 19:25:15

Haider

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


2015-04-02 05:25:31

David

@Barry - wow, thanks. Unfortunately, I don't use Outlook as I am hooked on Gmail. Nevertheless, thanks for the detailed response.


2015-03-31 05:38:25

Barry

@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.



2015-03-30 12:35:01

David Shalev

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


2015-03-30 05:51:54

Barry

@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


2015-03-30 01:53:49

Ahsan Shah

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.


2015-03-26 06:22:48

Barry

@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.


2015-03-25 07:23:08

Clare

Hi I am hoping the first =IF(L$3<(TODAY()+14),"<<<","")
will work, can I add an expired date to this also?

Regards
Clare


2015-03-24 06:07:46

Barry

@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.


2015-03-23 08:19:34

Clare

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


2015-03-13 12:07:38

Marc T.

Thank you this is exactly what I was looking for.


2015-03-10 14:58:36

Marie C.

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.


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.