+ Reply to Thread
Results 1 to 2 of 2

formula to compare a date to todays date

  1. #1
    Registered User
    Join Date
    03-09-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    1

    formula to compare a date to todays date

    I have a spreadsheet which has a column of dates, I would like to compare the dates with todays date to change the background colour of a cell to be either green, yellow or red, so that I can quickly look down the column to identify rows that require action.. i.e.

    If the date in the column is more than a week ahead no action is required and the cell would show green.

    If the date in the column is between 1 and 7 days ahead action is required and the cell would be yellow.

    If the date in the column has passed action is overdue and the cell would be red.

    Thanks
    Pete

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: formula to compare a date to todays date

    Hi PeteH, welcome to the forum.

    you can use conditional formatting to achieve what you describe.

    With your dates starting in A1:
    Select all your dates, then click Format - Conditional Formatting.
    Select Formula is and enter this formula:

    =A1<today()

    click Format and select red as the Pattern color and hit OK.

    Click Add - Formula is and enter this condition:

    =A4<=TODAY()+7

    set the format to yellow

    Add the third condition with

    =A4>TODAY()+7

    set the format to green and close the dialog boxes.

    If the first cell of your selection is not A1, then adjust the formulae accordingly.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1