Crazy Question re: Converting Dates

[SpineyNorman]

I have a large spreadsheet prepared by another party. One of the fields is for the term of a futures contract. To indicate an August 2011 contract, the originator input the date 8/11/2011 in the cell and formatted it with a cumtom format a la d-mmm so the output looks like this: 11-Aug.

This is fine for 2011 but to indicate an August 2012 contract they input the date 8/12/2011 and it looks like this: 12-Aug, even though for calculation purposes it is still year 2011. Why they did this I do not know!

But I would like a quick formula that could translate this second cell into a date of 8/1/2012 (which would have made sense to me in the first place). I may be making this harder than it should be, but I am going in circles mentally and decided to reach out.

Can anyone help?


Thanks in advance

 

[Jonmo1]

Welcome to the board..

I suspect the original preparer didn't actually enter 8/11/2011 or 8/12/2011
Rather, they only entered 8/11 and 8/12
Which excel interpraited and converted them as August 11th and 12th (of the current year).
NOT Aug 1st 2011 and Aug 1st 2012


Is there some other indication on the sheet to tell us what year it's supposed to be?

 

[SpineyNorman]

Welcome to the board..


Is there some other indication on the sheet to tell us what year it's supposed to be?

Unfortunately not - That is all I have.

 

[barry houdini]

If you have your date in A2 this formula will convert assuming that the day should be the year

=DATE(100+DAY(A2),MONTH(A2),1)

 

[SpineyNorman]

Nice - Thank you Barry