dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
7118
share rss forum feed


moh33rizgma

@78.101.120.x

[Excel] Automatically highlight expire dates that are 15 days fo

In Microsoft Excel 2010, is there a way to automatically highlight upcoming and past due dates?

For example, I have dates that Products will expire in a spreadsheet. I would like Excel to highlight the ones that are 15 days from expiration in yellow and the ones that are past the expiration in red. Is that possible? Even December 2012, even January 2013...please



JohnInSJ
Premium
join:2003-09-22
San Jose, CA

Re: [Excel] Automatically highlight expire dates that are 15 day

google for excel conditional formatting - that's what you're going to want to do.
--
My place : »www.schettino.us



mohriz

@78.101.120.x
reply to moh33rizgma

I have already searched there that is why I am here...

Thanks!

Anyone can help me to sort out???

Please....



JohnInSJ
Premium
join:2003-09-22
San Jose, CA

You don't understand conditional formatting?

»www.mrexcel.com/articles/due-dat···ting.php
--
My place : »www.schettino.us



H2OuUp2
Happy to be here
Premium
join:2002-03-15
Oklahoma City, OK
reply to moh33rizgma

downloadDateRange.xlsx.zip 6,728 bytes
Conditional Format
It's really easy. Here is a very simple example


Grey Area

join:2003-11-18
UK
reply to moh33rizgma

It's not easy if you never did it before.

Depends what version of Excel you are using, but basically the important function you need is;

=NOW()

This gives you today's date in a cell. So if you have your expiry date in cell A1, the formula

=NOW()-A1

Will tell you how many days are left until expiry.

If you add a column that has this in it for all your products, you can use the conditional formatting with two possible conditions;

=0 (less than or equal to zero) format in RED
=15 (less than or equal to 15) format in YELLOW

you might have to play around with what order you set the formats in, but that's the fun part!



H2OuUp2
Happy to be here
Premium
join:2002-03-15
Oklahoma City, OK

downloadDateRange.xlsx.zip 8,478 bytes
That is what the conditional format has in the example I attached. You don't have to add a column to have conditional format you can write the formula to point to the cell with the date.

Now if you did want to have a column that gives you days you can write a formula in it to tell you the number of days past due, or days till the due date and you can conditional it several different ways.

I attached an updated data range file that shows the different ways you can use conditional formatting.

Just check it out and it won't be long until you will be coming up with different ways to use conditional formatting.

I intentionally referred to different elements to show examples how you can use cell values and formulas, though there are many different ways (some easier) to come up with the same results.

Hope this helps.
--
He is no fool who gives up what he cannot keep, to gain what he cannot lose. - Jim Elliot