Formula/macro help
 

In Column "T" I have in text format dates in the following format dd mmm
yyyy. Reason for text format, I sometimes do not have the year. Is it
possible to write a formula in column "V" that if row "XX" and column "T"
are not blank and number of characters are greater than six, the right four
numbers are subtracted from the current year, providing a number of years
value. Or would this require a macro?

--

Regards
Michael Koerner

Formula/macro help
 

Michael, Not sure why you want to conditionally look for the character
count 6 (if you were testing for omitted yyyy then you would be
checking for LESS THAN 7 "mm/dd/" the formulae below will give you the
current year value if Column T is blank or has <7 characters..
otherwise will give the value of column T.

=IF(AND(T2<"",len(T2<7),TEXT(NOW(),"yyyy"),T2)

HTH,
Will

Formula/macro help
 

Tried your formula, and it was missing a ")" which I found after a couple of
tries (not to familiar with formula). which provided me with half of what I
want. Now that I have the current year I would like to subtract the yyyy
from "T2", giving me the number of years difference. Thanks for taking the
time to help.

--

Regards
Michael Koerner


"Will Cairns" wrote in message
ups.com...
Michael, Not sure why you want to conditionally look for the character
count 6 (if you were testing for omitted yyyy then you would be
checking for LESS THAN 7 "mm/dd/" the formulae below will give you the
current year value if Column T is blank or has <7 characters..
otherwise will give the value of column T.

=IF(AND(T2<"",len(T2<7),TEXT(NOW(),"yyyy"),T2)

HTH,
Will

Formula/macro help
 

Put this into the formulae where it's currently stated as
TEXT(NOW(),"yyyy")... however, if T2 is less than 7 characters in
length then there will not be a year value there ??


(TEXT(NOW(),"yyyy")-(TEXT(T2,"yyyy")))


Michael Koerner wrote:
Tried your formula, and it was missing a ")" which I found after a couple of
tries (not to familiar with formula). which provided me with half of what I
want. Now that I have the current year I would like to subtract the yyyy
from "T2", giving me the number of years difference. Thanks for taking the
time to help.

--

Regards
Michael Koerner


"Will Cairns" wrote in message
ups.com...
Michael, Not sure why you want to conditionally look for the character
count 6 (if you were testing for omitted yyyy then you would be
checking for LESS THAN 7 "mm/dd/" the formulae below will give you the
current year value if Column T is blank or has <7 characters..
otherwise will give the value of column T.

=IF(AND(T2<"",len(T2<7),TEXT(NOW(),"yyyy"),T2)

HTH,
Will

Formula/macro help
 

Once again, thanks very much for the help. If the T2 is less than 7
characters can the cell be left blank? This what I have now, which works if
there is 7 characters and enters FALSE if there is not 7 characters.

=IF(AND(T25<"",LEN(T25<7)),(TEXT(NOW(),"yyyy")-(TEXT(T25,"yyyy"))))

--

Regards
Michael Koerner


"Will Cairns" wrote in message
oups.com...

Put this into the formulae where it's currently stated as
TEXT(NOW(),"yyyy")... however, if T2 is less than 7 characters in
length then there will not be a year value there ??


(TEXT(NOW(),"yyyy")-(TEXT(T2,"yyyy")))


Michael Koerner wrote:
Tried your formula, and it was missing a ")" which I found after a couple
of
tries (not to familiar with formula). which provided me with half of what
I
want. Now that I have the current year I would like to subtract the yyyy
from "T2", giving me the number of years difference. Thanks for taking
the
time to help.

--

Regards
Michael Koerner


"Will Cairns" wrote in message
ups.com...
Michael, Not sure why you want to conditionally look for the character
count 6 (if you were testing for omitted yyyy then you would be
checking for LESS THAN 7 "mm/dd/" the formulae below will give you the
current year value if Column T is blank or has <7 characters..
otherwise will give the value of column T.

=IF(AND(T2<"",len(T2<7),TEXT(NOW(),"yyyy"),T2)

HTH,
Will