Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Formula for Max value | Excel Worksheet Functions | |||
Macro Formula for Max value | Excel Worksheet Functions | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Using the And formula in a VBA macro | Excel Programming | |||
how to add formula to this macro??? | Excel Programming |