# Date warning in Excel

Recently a friend who works in tourism asked me for a little Excel related help. He is working in the housekeeping department in a relatively large hotel and needed help with keeping track with the expiry date of products.

He had a report that had all the rooms in the hotel and expiry dates for each product. He wanted to see in a glance the products that will expire in 30 days, 15 and less.

#### Our example

We will work on this example, it is somewhat similar, his report has a much bigger number of rows and columns.

#### What we want to do

You can see in the image bellow that we have a column with the expiry date, we want the dates in that column to be colored in the following way:

• Red: for the products that have the expiry date earlier that 15 days
• Orange: for the products that are to expire in 15 to 30 days
• White: for the products that are to expire in more than 30 days

Of course we want this done automatically.

Before reading my approach bellow, take 5 seconds to think about how you would do it.

#### Solution – Red

Obviously we will use conditional formatting, we do not plan to hire someone full-time just to add color to cells on a daily basis. More exactly we will go into conditional formatting / highlight cells rules / less than.

Because we do not want to update the document on a daily basis, we will use a formula that knows what day is today.

We will format the dates “smaller than” today + 15. Yes, you guessed it, TODAY() gives us the current date. Yes, you can add a date with a number.

#### Solution – Orange

Moving on, we want to make the cells with a date 15 to 30 days later than today orange. This time, we will not look only at cells less than but rather at an interval.

#### The final result

And here is the final result:

#### Closing

I gave you an example in which we track the expiry date of certain products. We don’t need to stop at this example, because you have a richer imagination than me, let’s see in other ways we could apply what I described, tell me more in a comments section bellow.

1. anbu kumar

Is it possible the shortest expiry dates items automatically display in desktop or in scroll bar itself