Conditional Formatting of: dates within 1 month and dates before today.

**Luke Smith** · 06-18-2013, 06:52 AM

Dear All

I have joined this forum in the hope that someone can give me a simple answer to what seems to me a simple problem... Having browsed the web and excel help extensively I cannot find a simple answer that I can make work!

I am using Excel to display vehicle inspection due dates in a spreadsheet.

I would like dates BEFORE today (i.e. dates in the past) to be formatted a particular way, for example with a red fill in the cell.

I would like dates which are one month (or if easier 28 days) or less after today to be formatted another way, for example with a yellow fill.

If possible I would also like days between one and two months after today to be formatted another way e.g. with a green fill in the cell.

I have tried the various options offered in the Conditional Formatting/Highlight Cells/A Date Occuring... Menu but this only offers "Next Month"/"This Month", not within a given number of days.

I imagine there must be a simple way to do this for someone who knows the language and appropriate syntax for Excel, but I do not, and have more things to do: I am a mechanic, not a computer programmer!

Any help much appreciated, and happy just to be pointed to a link which tells me what to do if anyone knows where there is one.

Many thanks.

L Smith

**akhileshgs** · 06-18-2013, 07:00 AM

instead of going by dates, just use the direct conditional formating like Greater than, less than, between etc
and in the condition, you can put like
=today()
=today()+30
=today()+60
etc...

Attched sample file also for your help

conditional.xlsx

**Fotis1991** · 06-18-2013, 07:01 AM

Try in Conditional Formmating rules.

=A1<TODAY() >>>>>>>>RED

=A1<=TODAY()+28 >>>>YELLOW

=AND(A1<=TODAY()+60,A1>=TODAY()+30) >>>GREEN

**Luke Smith** · 06-18-2013, 07:20 AM

Hello All

Thanks for the amazingly fast replies-

I have tried the method suggested by akhileshghs and it enables me to get exactly what I wanted! Thanks especially for posting the sample worksheet, it was really helpful being able to see exactly what to do in each of the menus.

I have not, as yet, tried FOTIS1991's suggestion but will do so in the future.

Thank you both very much indeed- You have saved me much stress and allowed me to get on with my proper work!

Very much obliged.

Luke

**akhileshgs** · 06-18-2013, 07:23 AM

Originally Posted by Luke Smith

I have not, as yet, tried FOTIS1991's suggestion but will do so in the future.

Both the suggestions are same only...

Glad we could help you.
Please make this post as solved

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save