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

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...

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

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"


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640
View this thread: http://www.excelforum.com/showthread...hreadid=505234

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

Hi.. Thanks for the input. Can we change the cells to read AB, BD, and
CF...
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?

"wjohnson" wrote:


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"


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640
View this thread: http://www.excelforum.com/showthread...hreadid=505234

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

Not sure what you want with MM/DD/yyyy
Is the MM for January "JA or 01," but if you change the "MID" numbers
you can get anything you want.
Example: If cell A1 has January and you want to see Jan in Cell B1
using the MID Function
it would be MID(B5,1,3), it reads as follows: B5 is the Cell, 1 is the
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 it
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 AB, BD, and CF... - you can create a "AND" statement in a
Cell say "G" which will give you a TRUE or False and would look
something like:
=AND(A1B1,B1D1,C1F1)


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640
View this thread: http://www.excelforum.com/showthread...hreadid=505234

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

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

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

Format it to your liking


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=505234