Highlight Upcoming Dates in Excel

Do you use Excel to keep track of upcoming payments, or other dates? To make that list more helpful, you can highlight the dates that are coming up in the next couple of weeks.

highlightdates02

Compare Cells With a Simple Formula

With Excel's conditional formatting feature, you can highlight cells that have a specific value, or that meet other conditions. Recently, we used conditional formatting to show where a new date starts, in a list of dates.

ConditionalFormatSections04b

That red border was created if the date in column A was different from the date in the cell above. A simple formula checked the two cells, and created the top border if the cells were not equal:

=$A2 <> $A1

Calculate the Date Difference

In today's example, we want to highlight the cells where the date is between today and two weeks from today. We'll need a formula that does more than compare two cell.

First, the formula will have to calculate how many days are between today and the date in the cell. To do this for cell A2, the formula is:

=$A2 - TODAY()

highlightdates03

The result is –15, so that date was 15 days ago.

Check a Date Range

The next step is to see if the date difference is within the range that we want to highlight. That range goes from today, which is zero days difference, to the date two weeks from now, which is +14 days difference.

To check the date range with a worksheet formula, we'd use the AND function, combined with the days difference formulas:

=AND($A2-TODAY()>=0,$A2-TODAY()<=14)

highlightdates04

If the date difference is greater than or equal to zero, AND the date difference is less than or equal to 14, the result is TRUE. Those are the dates that we want to highlight.

Create the Conditional Formatting

To highlight the cells in the date range, we can use the same formula.

  1. Select date cells – cells A2: A11 in this example. Cell A2 is the active cell.
  2. On the Excel Ribbon's Home tab, click Conditional Formatting, then click New Rule
  3. In the New Formatting Rule dialog box, click Use a Formula
  4. In the formula box, enter this formula:
    =AND($A2-TODAY()>=0,$A2-TODAY()<=14)
  5. Click the Format button.
  6. Select formatting options (orange fill, in this example), click OK, twice, to close the dialog boxes.

highlightdates05

Now, the dates in the two weeks upcoming are highlighted in orange.

highlightdates06

Use a Cell Reference

To make the conditional formatting more flexible, you could use a cell reference in the formula, instead of typing the zero and 14.

For example, put your minimum and maximum days difference in worksheet cells, and name those cells. Here, the cells are named MinDays and MaxDays.

highlightdates07

Then, change the conditional formatting formula to use those cell references:

=AND($A2-TODAY()>=MinDays,$A2-TODAY()<=MaxDays)

After changing the formula, you can change the days on the worksheet, and the conditional formatting will automatically adjust.

For example, change the MinDays to -7 and MaxDays to 21, and the date range goes from 1 week ago, to three weeks from now.

highlightdates08

More Conditional Formatting Examples

You'll find many more conditional formatting examples and tutorials on the Contextures website.

______________

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

2 comments to Highlight Upcoming Dates in Excel

  • Domenic Varone

    I am entering service dates for truck example:
    PM date Next PM
    Dec 25 2012 Mar 25 2013
    I want to highlight the next PM date if it goes over 90 days.
    Can you help?
    Thanks

    • Utkarsh Shah

      Hi,
      Domenic Varone. You can simply select Next PM column. Go to Conditional formatting & select New Rule. In that again select "Use a Formula to determine which cell to Format " & Enter following formula. & Just select your format means Red Text or Fill Text with color. & Its Done.
      Here is Formula-

      =(B1-A1)>90

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>