Solved

Formula to add dates for conditional formatting in Excel 2010

Posted on 2013-11-05
6
410 Views
Last Modified: 2013-11-11
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
Comment
Question by:fabi2004
  • 3
  • 3
6 Comments
 
LVL 50

Expert Comment

by:barry houdini
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

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

Expert Comment

by:barry houdini
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:fabi2004
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

by:
barry houdini earned 500 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

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Moving Excel to AaaS 4 37
Fixing a embedded format 7 29
excel connection properties parameters grayed out 5 27
nested if statement in excel help 4 12
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now