Add 1 year to date and display text MMYYYY

[LaKartoffelita]

Hi everybody,

I am new to this forum and it already helped me a lot just by using the search, but this time I can't find an already existing thread with a solution that fits my problem. I am quite new to excel and just started using more complex formulas (and don't know much about macros let alone VBA).

So here's my problem: I have a cell that contains a certain date and I need a way to add one year but instead of displaying a date I need the output to be a certain text according to the month. Here's an example:

A1 = 01.06.2015
B1 = 2016 P6 (The date behind this would be 01.06.2016)

Right now I managed the first part by using the following formula...
=DATE(YEAR(A1) + 1; MONTH(A1); DAY(A1))
...and was thinking to add a TEXT formula with it to display "062016" (not 100% what I need, but this would work as well), except I'm not sure how to combine those two.

I also searched the forum and found something that is very close to what I need...
http://www.mrexcel.com/forum/excel-questions/774759-date-range.html
...but can't make it work. In my Excel this is what I get "07YYYY" (Also this adds a months, not a year...)

Is there a way to make either one of these options work or is there another way to get what I need?

Thanks a lot

 

[Jonmo1]

If A1 contains a valid date, try

=TEXT(EDATE(A1;12);"mmyyyy")

 

[FormR]

A1 = 01.06.2015
B1 = 2016 P6 (The date behind this would be 01.06.2016)

If you wanted the result as per the example quoted above, you could try:

=YEAR(A1)+1 & " P"&MONTH(A1)

 

[LaKartoffelita]

Fantastic! Thank you! The second one worked and is the 100% solution I was looking for.

However the first suggestion doesn't work in my file - it returns 00yyyy.
How can I make sure I entered a valid date? (I'm guessing that might be the problem...?!)
(I'm trying to actively improve my excel knowledge and would like to understand and make this option work as well )

 

[Jonmo1]

However the first suggestion doesn't work in my file - it returns 00yyyy.

It must be related to your regional/language settings
You don't have d=day m=month y=year

Look in Windows Control Panel, regional and language.
What is shown as the Short Date format?

Take the formula, and replace the m's with whatever letter represents the Month, and the y's with whatever letter represents the Year.
=TEXT(EDATE(A1;12);"mmyyyy")

 

[LaKartoffelita]

That's it. It's German.
Changed the Y to J and it works
(M stays M)

Thanks a lot!!

 

[Jonmo1]

You're welcome.