Excel 2010 Conditional Formatting (Date)

Hi,

A quick one. How to make a conditional formatting in a cell if the date entered is 1 month or less prior to date in the cell to the left of the cell where the date is entered and no formatting if cell to the left is empty.

Say: F9 has date 05.09.2013 and then enter 20.08.2013 in G9 it becomes red (as date is less than a month prior to date in F9), and a bonus would be gradually more red the closer to the date in F9. If nothing in F9 then no formatting of G9.

Best regards
Jesper
Jesper E MagnussenProject DirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
If you have dates in the format as in the question, ie with dots between the day month & year, excel won't recognise as dates.

if you use a recognised format in both cells, eg dd/mm/yy, then the comparison is a whole lot simpler.

Thanks
Rob H
0
Jesper E MagnussenProject DirectorAuthor Commented:
Hi,

I believe this is a problem only in the English version, I'm working with a Norwegian version. But I will adjust the date-format to fit my language, if only someone can help me with the formula or VBA code.


Say: F9 has date 05/09/2013 and then enter 20/08/2013 in G9 it becomes red (as date is less than a month prior to date in F9), and a bonus would be gradually more red the closer to the date in F9. If nothing in F9 then no formatting of G9.


Brgds
Jesper
0
Chris MillardCommented:
You could format the cells with dates in them as TEXT, then you can do conditional formatting using the DATEVALUE function.
0
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Rgonzo1971Commented:
Hi,

pls try  

=EDATE(F9;-1)<G9

Open in new window

See attachment
Regards
edate.xlsx
0
terencinoCommented:
and for the bonus, this get redder for each week closer to the mark..
CF-Dates.xlsx
0
Jesper E MagnussenProject DirectorAuthor Commented:
Hi,

Is it necessary to format the cells as text? Would rather it was formatted as date as the values are used in other sheets as links.

Brgds
Jesper
0
Jesper E MagnussenProject DirectorAuthor Commented:
Hi again,

Terencino's suggestion works for the cell where I copy the format to, but how can I use the same formatting for all the cells in the column? When I try to use the format brush it keeps the reference to the cell where I copied the formatting from your sheet to.

Brgds
Jesper
0
Jesper E MagnussenProject DirectorAuthor Commented:
Here's a sample where I have manually changed the references in each conditional formatting...

Brgds
Jesper
Kopi-av-CF-Dates.xlsx
0
terencinoCommented:
Hi Jesper, I changed the referencing from $F$9 to $F9 (absolute to partial) and dragged down and made a couple of manual adjustments. Should be Ok to copy to the rest of your cells now!
Kopi-av-CF-Dates.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jesper E MagnussenProject DirectorAuthor Commented:
Works like a charm... thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.