How to calculate and highlight expiration dates

In this video we'll look at how to calculate and highlight expiration dates.

Let's say your company has started a membership program of some kind and your boss just sent you a set of data. She's given you a list of 1,000 people that have renewed a membership in the last year or so, and she's looking for several things.

First, she wants you to calculate an expiration date one year in the future, on the last day of the same month that membership was last renewed.

Second, she wants to see how many days remain before expiration.

Third, she wants to see a status of "expired" for any memberships already expired, and "expiring soon" for any memberships expiring in the next 30 days.

Finally, she said it would be nice to see "expired" members highlighted in pink, and those "expiring soon" highlighted in yellow.

Also, can she have it before her lunch meeting at noon?

Umm, sure.

First, let's convert this data to a proper Excel Table. This will make the formulas much easier to enter, since Excel will automatically copy them down as we go.

Now let's calculate the expiration dates. These are supposed to be at the end of the same month one year later, but let's just use a simple hack first to get "in the ballpark." As you've seen in earlier videos, dates are just serial numbers, so we can just enter a formula that adds 365 days to the "Renewed" date.

This is a good start. We can finish roughing out the solution and come back to fix this later.

When you're solving a more complex problem in Excel it's a good idea to validate the overall approach, and then come back to the details at the end. You don't want to get stuck on a small thing right at the start, especially if the approach may change.

Now that we have an expiration date, we can calculate "Days left." This needs to update automatically in the future, so we'll use the TODAY function which always returns today's date.

The formula we need is simply E5 minus TODAY(). When I hit Return, we get the days remaining before expiration. Negative numbers indicate a membership already expired.

For status, we'll use a simple nested IF formula. If days left is less than zero, the membership is expired. Otherwise, if days left is less than 30, the status should be "expiring soon." Otherwise, the status is nothing.

=IF(F5<0,"Expired",IF(F5<30,"Expiring soon","")) 

Next, we need to create conditional formatting rules that highlight these values.

First, select the data, and set the active cell to the upper right corner. Then, create a formula rule that tests the active cell for the value "expired." The column must be locked.

Now repeat that same process for memberships that are "expiring soon."

This is looking pretty good. We just need to fix the expiration dates to land on the last day of the same month.

Well, it turns out that there's a cool function in Excel called EOMONTH (for end of month) that gets the last day of a month in the past or future.

Start date is the date renewed, and months is "12".

And there you have it, everything that your boss wanted, and you still time for a cup of coffee before lunch.