how do i get excel to read 03/07 as March 2006 instead of 03 Jul?

D

dt1976

i have received a spreadsheet with dates labled as text mm/yy. i was able to
convert it to a number, but when i try to add years to it, excel converts it
to dd/mm/2008 then ads the year.
for example if i want to add 2 years to 03/07, i get a result of 03 Jul 2010
instead of 1 Mar 2009.
 
N

Niek Otten

You'll have to either redo the conversion:

=DATE(RIGHT(A1,2)+2000,LEFT(A1,2),1)

or convert the wrong dates:

=DATE(MONTH(A1)+2000,DAY(A1),1)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|i have received a spreadsheet with dates labled as text mm/yy. i was able to
| convert it to a number, but when i try to add years to it, excel converts it
| to dd/mm/2008 then ads the year.
| for example if i want to add 2 years to 03/07, i get a result of 03 Jul 2010
| instead of 1 Mar 2009.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

dd/mm/yy if i change to some other format 2
Convert '12-03 to 12-03 date 4
How to set the cell for date? 7
How to Convert 24/03/2010 as text into 03/24/2010 date format 2
Entering date as MM/DD/YY no longer works, why? 1
How to get Excel to open in 07 not 03 from explorer 1
How to display yy/mm/dd 06/03/04 in excel and not get 06/02/2065! 4
how to get "EndDate" of a project? 2

Top