CODEWITHSUNDEEP
date-formatexcel-2013date-conversion
Yesenia Garcia
Yesenia Garcia

Reputation: 13

Convert Date Format in Excel from foreign date

I converted a PDF to Excel file and now my date column is being recognized as Danish dates. When I pivot or try to format them, the format stays in Danish.

How can Danish format be converted to English (US)?

I've tried changing the language in Format Cell settings with no success. Some convert just fine while others don't.

Using TEXT([@Date],"MM/DD/YYYY") the results are:

     Original
     05-06-14 
     05-06-14
     05-04-14
     04-30-14
     04-29-14

     Converts to
     06/14/2005
     06/14/2005
     04/14/2005
     04/30/2014 (correct)
     04/29/2014 (correct)

Help?

Upvotes: 0

Views: 2381

Answers (2)

MFarooqi
MFarooqi

Reputation: 1034

It's Easy.. Just select the column.. and change the cell formate by pressing (ctrl+1) and change it to Date.. It will ask 19xx or 20xx.. select the appropriate years.. you'll get it..

I even tried and I get the perfect result what you are actually looking for..

        **A**           **B**
        *Number/text*   *Date*
1       05-06-14       =TEXT(A2,"mm/dd/YYYY")
2       05-06-14         
3       05-04-14         
4       04-30-14         
5       04-29-14

Repeat the above formula on B1 on bottom rows.. you'll get result..

Otherwise.. you can simply select column A and change the type to Date.. and then follow the step one as i said on top

Upvotes: 0

BrettFromLA
BrettFromLA

Reputation: 916

If you can format the whole column with the messy values (column "A") as text, and format the next column (column "B") as Date, you can put this formula into the Date-formatted column (column "B").

=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,4,2)&"/20"&RIGHT(A1,2))

Upvotes: 0

Related Questions