How to convert dd.mm.yyyy date format to yyyy-mm-dd in Excel 2007?

Question

My customer entered date in dd.mm.yyyy format. As I want insert all the data in mysql database, I want the date in yyyy-mm-dd format.

I tried to format using Excel custom date format and built-in function like =text(A1,"yyyy-mm-dd") but it did not work, I still got the same format (it still shows dd.mm.yyyy).

For example, I typed 30.10.2010 in any cell, and tried changing the format to yyyy-mm-dd to be 2010-10-30, but it did not work.

I also looked for questions in Super User stack and Google but I could not find solution.

Answer 1

I tried it with 30/01/2010

SUBSTITUTE(A1,".","/")

and then I put in

=TEXT(B1, "yyyy-mm-dd").

The result was expected.

I suspect the issue is the cell you are doing this too is not in date format.

For example, if I change my value to 30.01.2010 then the value is then duplicated (in the same manner you describe)

Or, you could keep it as

=TEXT(A1, "yyyy-mm-dd")

And update all the . to / with a quick macro

Option Explicit
Sub ReplaceDate()

Dim row As Integer
row = 1

Do While (Range("A" & row).Value <> "")

    Dim val As String
    val = Range("A" & row).Value

    Dim i As Integer

    Dim result As String
    result = ""

    Dim spl() As String
    spl = Split(val, ".")

    If (UBound(spl) > 0) Then


    For i = 0 To Len(val)

    Dim r As String
        result = result & Replace(Mid(val, i + 1, 1), ".", "/")
    Next i

    End If

    If result <> "" Then

        Range("A" & row).NumberFormat = "@"
        Range("A" & row).Value = result

    End If

row = row + 1
Loop

End Sub

How do I add VBA in MS Office?

Answer 2

Right click on the cell -> Format Cells -> Choose Category Date -> Choose from Local Settings English (UK) -> select format yyyy-mm-dd

That's without changing the localization settings of the whole OS as above mentioned.

Answer 3

All you have to do is change you region and language to UK,

Start menu, control panel, region and language, format to "English (United Kingdom)" and then set the short date to "dd/MM/yyyy" and say apply and Ok.

The next time you use excel just change your cells to "date" then format the cell by clicking on the down arrow on the "number format" scroll to the bottom and select (More number formats... ) find the "date" option in the pop up and choose the first date option it looks like (*14/03/2001) change all the cells you need the date to look that and ta da... It's done.