Text to Date function in Excel

1
votes

In Excel I have a row with many dates in it:
Oct-13 Nov-13 Dec-13 Jan-14 Feb-14 Mar-14 Apr-14 May-14

However they are coded as: =TEXT(AD42,"MMM-YY") =TEXT(AD41,"MMM-YY") =TEXT(AD40,"MMM-YY") =TEXT(AD33,"MMM-YY") =TEXT(AD31,"MMM-YY") =TEXT(AD28,"MMM-YY") =TEXT(AD26,"MMM-YY") =TEXT(AD23,"MMM-YY"), respectively.

I need to increase all of their numbers by one. So Oct-13 is now Oct-14. They are all formatted as general. I have tried to change the numbers multiple times but the majority of times it gets coded as Jan-00. This was a file given to me that I did not create, but simply trying to manipulate. I don't really think Excel has the greatest continuity with the Date function.

In the end I simply took the long way and just did: 'MMM-YY Thank you all

3
excel

3 Answers

2
votes

You could just add 365 to the cell containing the date (for example): 

=TEXT(AD42+365,"MMM-YY")

Or if you would like to instead accommodate for leapyears, just pull out the year number and add 1 to it: 

=LEFT(AD42,4)&(RIGHT(AD42,2)+1)
0
votes

You could just add 1 to the year of the date:

=TEXT(DATE(YEAR(AD42)+1,MONTH(AD42),DAY(AD42)),"MMM-YY")
0
votes

Because of the leap year issue, I'd go with this solution instead of just adding 365 days: 

=LEFT(TEXT(AD42,"MMM-YY"),4)&INT(RIGHT(TEXT(AD42,"MMM-YY"),2))+1