Second, she wants to see how many days remain before expiration.
Also, can she have it before her lunch meeting at noon?
First, let’s convert this data to a properExcel Table.
Now let’s calculate the expiration dates.
This is a good start.
We can finish roughing out the solution and come back to fix this later.
Now that we have an expiration date, we can calculate “Days left.”
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 simplenested 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 createconditional formatting rulesthat highlight these values.
First, go for 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.
Start date is the date renewed, and months is “12”.
