Dates in excel

[NSalzmann]

Hello. I have a problem and hope that somebody can help me. I have an excel column which gives me the date since when a product is in the store. Unfortunately the date is given in mm/yy/dd and excel has problems to recognize it. I cannot work with this data as the field gives me a random number any time I want to do something with it. Ex. <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:date Month="8" Day="6" Year="2025">06/08/25</st1:date> becomes 45875. So I have two questions.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>

• how can I convert mm/yy/dd into for example dd/mm/yyyy?
• How can I get excel to not change it into random numbers?

<o> </o>
Would be great if anybody knows the answer. Thanks in advance.
<o> </o>
Nils<o></o>

 

[DonkeyOte]

what should 06/08/25 be -- 25th August 2006 or 25th June 2008 (presuming the latter)

is it stored as text or number -- you mention if you work with it becomes number but are you converting the text to number using 0+ ?

 

[Richard Schollar]

Hi

The following will work if these 'dates' are actually being stored as text:

Select the column with these dates and go Data>TextToColumns. When the dialog opens, hit Next twice and on the third screen select a date import format of MYD and click Finish. This should convert your dates to proper dates that you can then format as required via Format>Cells.

 

[NSalzmann]

Hello. Thanks for the help but I still have the same problem. I have never seen this. When I want to format the cell only the date option shows me a number close to what it is all other options give me a random number (or at least I do not see a relation). I tried the way of RichardSchollar but it does not change. How can I post an excel file? Then I could show you what I mean.

Thanks for the effort

 

[DonkeyOte]

well assuming they are stored as numbers...if entry in A1 then below in B1

=DATE(2000+MONTH(A1),DAY(A1),YEAR(A1)-2000)

should give you a date value which you can format as dd/mm/yyyy

 

[NSalzmann]

Thanks a lot. Guess it must have been stored as numbers. It work.

Once again. Thanks for your help.