• Status: Solved
• Priority: Medium
• Security: Public
• Views: 474

# Formula to add dates for conditional formatting in Excel 2010

I am creating a spreadsheet to track expiration dates.  I have several columns of dates.  I need to highlight the cell red if the date is 2 or more years ago or if the cell is blank; highlight it yellow if the date will be 2 years old within the next 30 days, and highlight the cell green if the date is less than 2 years minus one month old.

I can set up the conditional formatting but my formulas aren't working right.  I've been trying it with the IF function but I have a hard time manipulating dates.

Can anyone help me with the formulas?
0
fabi2004
• 3
• 3
1 Solution

Commented:
You can get today's date - 2 years (24 months) by using EDATE function, i.e. =EDATE(TODAY(),-24)

....so you can format all entries green by default (using regular formatting) and then use these 2 conditions in conditional formatting for red and yellow

=A2<=EDATE(TODAY(),-24)

and

=A2<=EDATE(TODAY(),-23)

...thus highlighting dates older than 2 years (and blanks) red....and dates within the next month yellow - assuming dates start at A2 - see example attached within random dates in A2:A25 - press F9 to re-generate random dates

regards, barry
cf-dates.xlsx
0

CIOAuthor Commented:
Will anyone be able to use the workbook?  Specifically, any PC without the Analyses Toolpack installed?
0

Commented:
EDATE is a built in function in Excel 2010 and Excel 2007, Analysis ToolPak isn't required for it to work.

Analysis ToolPak functions can't be used in conditional formatting in Excel 2003 or earlier versions. If you have anybody who needs to use 2003 then you can use this formula for red

=DATEDIF(A2,TODAY(),"m")>=24

and this one for yellow

=DATEDIF(A2,TODAY(),"m")>=23

See attached where I kept the original dates in column A (and conditional formatting with EDATE) but also replicated the same dates in column E. Column E conditional formatting uses DATEDIF formulas similar to above....

regards, barry
cf-dates2.xlsx
0

CIOAuthor Commented:
I posted early this morning, but it doesn't look like it went through.

I am having a hard time getting the formulas to populate correctly to the rest of the workbook.  The cell references do not change when I paste the formatting to other cells.

Can you help me or tell me how to populate entire columns with the correct conditional formatting formulas?
0

Commented:
I usually select the whole range first, and then apply the formula that applies to the top left cell of that range, so if you have dates in A2:C100 then select that range and apply the formula that applies to A2.

If you want to apply to a whole column, e.g. column D then select the column and apply the formulas as they effect the first cell in that column (i.e. reference D1)

Alternatively you can adjust the "applies to" range in conditional formatting to extend the range you used initially...but if you change the top left cell of the "applies to" range then the formula will also change and you might have to adjust it.

If you want to copy conditional formatting across columns then its best to use "format painter". In my example sheet, for instance, if you want to copy the column A conditional formatting to column J then do this

Select source column (col A)
Click on "format painter" button (small paintbrush at left hand side of "home" tab"
Click on target column (col J)

Note that if you are copying you need to make sure that the source formulas have relative references (A2 not \$A\$2) otherwise they won't adjust properly to the new location.

If you still have problems post back or attach a sample which shows the problem

regards, barry
0

CIOAuthor Commented:
Thank you very much for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.