How to extract last 2 digits from date year in next column?

[mso3]

Hi friends,
I want to extract the last 2 digits of date year in next column. For ex. suppose the date is in column O then the output should be in column P.

I don’t want to change the date each year but I want to change the number in column P each year. 10 to 11, 11 to 12, 12 to 13 and so on each year.

If there is a remark in column Z ‘Left’ then no change in the number in column P.
Thanking you,

[Ace_XL]

use

=RIGHT(YEAR(O6),2)
or
=TEXT(O6,"yy")

[mso3]

Hi Ace_XL,
Thank you. The first part of my problem is solved but what about the remark in column Z 'Left'. I want to increase the output in column P yearly until there is a remark in column Z 'Left'.

Waiting to receive a solution for increment of number until the 'Left' remark in column Z.

Thank you.

[Glenn Kennedy]

Try this

=IF(Z6="left",O6,TEXT(O6,"yy"))

[Ace_XL]

Use

=Text(O6,"yy")+(z6<>"Left")

[mso3]

Hi Glenn,
Thank you. It's working fine but I have made a little revision in the formula as follows because if there is a remark 'Left' in column Z then there is a date value in column P.

Please Login or Register  to view this content.

Only I want to know that will the number in column P remain unchange next year 2015?

Thank you.

[mso3]

Hi Ace_XL,
No! It's changing the year now. It should be change next year. It's not changing the value in column P if there is a remark 'Left' in column Z which is ok.

Suppose if there is a date in column o6: 01-05-2011 then there should be 11 in column p and next year 12 and not now.
If there is a remark 'Left' in column Z then the number in column P should be 11.

Thank you.

[Glenn Kennedy]

Confused...

Cell P7. Now (1st December 2014) you want this to show 12/06/2011. Next year (1st January??), or on the anniversary of this event (12/06) -WHICH?? - you want the cell entry to change to 12/06/2012? is that correct? If not, please take a deep breath and explain again...

[mso3]

Hi Glenn,
Thank you for your deep concern to solve my problem.

Please don't confuse. I have made the necessary changes manually in the attached workbook for your better understanding to achieve the target. At present the entries are in different columns but I want to change it automatically each year in column P only and not other columns as shown in the workbook. There is a another data in my original workbook.

If it is not possible by formula then a vba solution will be accepted.

Hope this will clear your confusion.

Thank you.

[Glenn Kennedy]

There still seems to be some information missing. cell P6. Why did this not change to 12 in 2012? Why did it not change to 13 in 2013? How do you know in what year to begin increasing the number?

[mso3]

Hi Glenn,
The number in p6 is a extract of the last 2 digits in the date column ‘O’.
If there is a 12 in the date then it changes to 12 in column P. I have a formula which converts the number into from-to year so I want this number. For example: If there is 12 in column P then it will change to 2012-2013 in column Q. If there is 11 in column P then it will change to 2011-2012 in column Q.

I will know from which year the number is increasing by the date in column O which is static.

Hope this will clear your doubt.

Thanking you,

[Glenn Kennedy]

I don't understand the logic behind all this... But... Try this out.

[mso3]

Hi Glenn,
Thank you. Now it's working absolutely fine as per my requirement.

Thank you very much for solving my problem.