[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Formula to add dates for conditional formatting in Excel 2010

Posted on 2013-11-05
Medium Priority
448 Views
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
Question by:fabi2004
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3

LVL 50

Expert Comment

ID: 39626062
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

LVL 1

Author Comment

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

LVL 50

Expert Comment

ID: 39626090
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

LVL 1

Author Comment

ID: 39628828
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

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 39628903
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

LVL 1

Author Closing Comment

ID: 39638801
Thank you very much for your help!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
###### Suggested Courses
Course of the Month14 days, 23 hours left to enroll