Ask a question Report Reply to this topic

Conditional format to highlight cell based on date [Solved/Closed]

kieferschild 2387 Posts Sunday October 5, 2008Registration dateModeratorStatus April 12, 2018 Last seen - Aug 26, 2016 at 07:30 AM - Latest reply: Mazzaropi 1826 Posts Monday August 16, 2010Registration dateContributorStatus April 15, 2018 Last seen

- Aug 26, 2016 at 08:40 AM

Excel Ask a question Report Reply to this topic

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

Report Respond to Mazzaropi

Mazzaropi 1826 Posts Monday August 16, 2010Registration dateContributorStatus April 15, 2018 Last seen - Aug 26, 2016 at 07:46 AM

Helpful

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

Report Respond to kieferschild

kieferschild 2387 Posts Sunday October 5, 2008Registration dateModeratorStatus April 12, 2018 Last seen - Aug 26, 2016 at 07:50 AM

Helpful

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.

Report Respond to Mazzaropi

Mazzaropi 1826 Posts Monday August 16, 2010Registration dateContributorStatus April 15, 2018 Last seen - Aug 26, 2016 at 08:10 AM

Helpful

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

Report Respond to kieferschild

kieferschild 2387 Posts Sunday October 5, 2008Registration dateModeratorStatus April 12, 2018 Last seen - Aug 26, 2016 at 08:21 AM

Helpful

Hi,

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

Report Respond to Mazzaropi

Mazzaropi 1826 Posts Monday August 16, 2010Registration dateContributorStatus April 15, 2018 Last seen - Aug 26, 2016 at 08:32 AM

Helpful

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

Report Respond to kieferschild

kieferschild 2387 Posts Sunday October 5, 2008Registration dateModeratorStatus April 12, 2018 Last seen - Aug 26, 2016 at 08:35 AM

Helpful

That my friend is perfect.

Thank you very much.

Report Respond to Mazzaropi

Mazzaropi 1826 Posts Monday August 16, 2010Registration dateContributorStatus April 15, 2018 Last seen - Aug 26, 2016 at 08:40 AM

Helpful

You're welcome!

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

Ask a question