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:

Download

You can download the document used by me here.

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.