Solved

Formula to add dates for conditional formatting in Excel 2010

Posted on 2013-11-05
6
407 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

707 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