Conditional format to highlight cell based on date

Solved/Closed
Blocked Profile - Aug 26, 2016 at 07:30 AM
Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
- Aug 26, 2016 at 08:40 AM
hi guys!

I need some help.

I'm creating a spreadsheet to show when car MOT expires.

I have a column (F) which has MOT expiry dates in.

I have a CF which highlights the cell red if the date is more than TODAY. This worked fine.

What I would like is, once the MOT expiry date gets within 15 days before todays today, i want it go to orange.

thanks.

7 replies

Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
147
Aug 26, 2016 at 07:46 AM
kieferschild, Good morning.

Try to use:

A1 --> EXPIRE DATE
THEN..

=AND(A1<=TODAY();TODAY()-A1<=15)

Use to COLOR ORANGE

Is this what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0
Blocked Profile
Aug 26, 2016 at 07:50 AM
Sorry, you'll have to excuse how much of a noob I am when it comes to Excel.

My dates are all in column C.


0
Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
147
Aug 26, 2016 at 08:10 AM
Kieferschild, Good morning.

As you set a RED color, you now how to create a rule.

Now, you'll create a NEW RULE.

Select at C column the EXPIRE DATES
Suppose: C2:C20

CF MENU --> NEW RULE --> USING FORMULA

Type it at field: =AND(C2>=TODAY(),C2-TODAY()<=15)

Choose ORANGE as fill format
OK

Please, tell us if it working now.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0
Blocked Profile
Aug 26, 2016 at 08:21 AM
Hi,

for some reason when it fills in the cell, it is filling in the cell below where i've put the data.
0

Didn't find the answer you are looking for?

Ask a question
Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
147
Aug 26, 2016 at 08:32 AM
Kieferschild,

I did an example for you.
http://speedy.sh/7kDkN/26-08-2016-en-Kioskea-Expire-date-CF-OK.xlsx

Is this what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0
Blocked Profile
Aug 26, 2016 at 08:35 AM
That my friend is perfect.

Thank you very much.
0
Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
147
Aug 26, 2016 at 08:40 AM
You're welcome!

Thanks for the feedback.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0