Expiration date based on 5 years and Birth month,day ...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kane - 26 Jan 2006 06:25 GMT

>B5 Birthdate ( MM/dd/yy format )
>D5 Issue Date ( MM/dd/yy format )
>F5 Expiration Date ( MM/dd/yy format )

A particular licence is issued for a time period of 5 years, expiring on the
Birth month, and Birth day, five years from the Issue date.
Any thoughts would be appreciated.
Thanks...

Reply to this Message

wjohnson - 26 Jan 2006 07:34 GMT

For the following formula format your cells as follows:
Column A - Birthdate - Format as Text
Column B - Issue Date - Format as Text
Column C - Is the Experiation Date - Format as General (or the formual
will not work if it is formated as "Text" - don't need to use the date
function).
In column C - use the following formula:
=CONCATENATE(MID(A2,1,6),MID(B2,7,2)+5)
A______________B____________C
Birth date______Issue Date____Expires
05/01/47_______06/20/05_____05/01/10

If you want to "add" more "time" to the expire date - just change "+5"

Signature

wjohnson

Reply to this Message

Kane - 26 Jan 2006 13:00 GMT

Hi.. Thanks for the input. Can we change the cells to read A>B, B>D, and
C>F...
I require that the birthdate cell (B5) be MM/dd/yy format, "October 01,
1947". Could another cell (ie; B10) read (B5), and then B10 is used in the
formula?
I would really prefer to have the results printed as MM/dd/yyyy in F5. Can
we do the calculation elsewhere and read/copy the result back t o F5?

> For the following formula format your cells as follows:
> Column A - Birthdate - Format as Text
[quoted text clipped - 9 lines]
>
> If you want to "add" more "time" to the expire date - just change "+5"

Reply to this Message

wjohnson - 26 Jan 2006 16:27 GMT

Not sure what you want with MM/DD/yyyy
Is the MM for January "JA or 01," but if you change the "MID" number
you can get anything you want.
Example: If cell A1 has January and you want to see Jan in Cell B
using the MID Function
it would be MID(B5,1,3), it reads as follows: B5 is the Cell, 1 is th
position of the first character you want to read, 3 is how many.
Just "play around" with the set of numbers following the MID and i
will give you an idea of how it works.
=CONCATENATE(MID(B5,1,6),MID(D5,7,4)+5)
B______________D____________F
Birth date________Issue Date____Expires
05/01/1947_______06/20/2005_____05/01/2010

For the A>B, B>D, and C>F... - you can create a "AND" statement in
Cell say "G" which will give you a TRUE or False and would loo
something like:
=AND(A1>B1,B1>D1,C1>F1

Reply to this Message

Cutter - 26 Jan 2006 16:42 GMT

Using the cells from your example simply use this in F5:

=DATE(YEAR(D5)+5,MONTH(B5),DAY(B5))

Format it to your liking

Signature

Cutter

Reply to this Message