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 1000 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 the membership was 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?
First, let's convert this data to a proper Excel Table. This will make 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, 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 now calculate days left. This needs update automatically in the future, so we'll use the TODAY function, which always returns todays date.
The formula is simply E5 minus TODAY(). When I hit return, we get 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.
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 good. We just need to fix the expiration dates to land on the last day of the month.
Well, it turns out that there's a cool function called EOMONTH (for end of month) that gets the last day of a month in the past or future.
Start date is date renewed, and months is 12.
And there you have it, everything your boss wanted, and you still time for a cup of coffee before lunch.