Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Formula to add dates for conditional formatting in Excel 2010

Posted on 2013-11-05
6
Medium Priority
?
464 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 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

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

571 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