Credit cards as MM/YY. How to tell WHEN they expire.

Q: I am creating a spreadsheet with customer card details on and when I input the card expiry date entered as MM/YY I want it to change colour font if the card has expired using a formula. The only problem is that the format of the cells is set as Text as when you enter a 0 it automatically disappears i.e. 04/09. Please help as I cannot find an answer anywhere. Also is it possible to then have it so that if it is due to expire in say 30 days time to turn it to a different colour?


A: You have a column of credit card expiration dates that are TEXT in the format MM/YY. If they're all exactly MM/YY you can do this:

Assuming the EXP date is in A1, put this in B1:

=LEFT(A2,2)

That gives you the MONTH. Now put this in C1:

=RIGHT(A2,2)

Now you have the YEAR. Put it all together in D1:

=DATE(C2+2000,B2,1)

That should give you an actual DATE value with the first of that month. If you want the first day of the NEXT month then say this:

=DATE(C2+2000,B2+1,1)

If a card says the EXP date is 04/09 then that usually means it's VALID THRU 04/09 which means it actually expires 05/01/09.

Now that you have an actual DATE value, you can go ahead and perform normal CONDITIONAL FORMATTING on that field to change its color if it's less than 30 days away, for example.

To figure out how many days from now that card expires, just say this in E1:

=D2-TODAY()

Format that cell as a number, and you're all set. If you need more help with conditional formatting, see this link:

http://599cd.com/tips/excel/conditional-formatting