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

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.

enter image description here

Mawia HL

Posted 2014-08-06T09:01:44.150

Reputation: 117

2Any problem with just setting the format on the cell? – a CVn – 2014-08-06T09:04:43.807

If I manually type the date, everything is OK. Format setting seems OK. – Mawia HL – 2014-08-06T09:06:10.773

1But what is the format? Excel will often auto-detect and set the format on the cell accordingly. Is the cell format text or date? If the latter, what happens if you simply change it to a date format more in line with what you want as output? – a CVn – 2014-08-06T09:07:07.180

The cell format is general, and I changed it to date, but it did not work. Just type 30.01.2010, and tried to change the format, but it is not working. So I wonder how we can change. – Mawia HL – 2014-08-06T09:09:04.993

1I tried, this works fine =TEXT("30/01/2010", "yyyy-mm-dd") - What does "not work" actually mean? Do you get an error message, does anything display, does it return nothing? – Dave – 2014-08-06T09:49:09.343

=Text(A1,"yyyy-mm-dd"), does this work? And this is what I am asking. – Mawia HL – 2014-08-06T09:57:06.827

@DaveRook, thanks for your input. I also tried =Substitute(A1,".","-") and then tried to change the format from custom cell format, still nothing changes, There is no error message also. – Mawia HL – 2014-08-06T10:05:49.917

2You say nothing changes, but what do you see in the cell with =Text(A1,"yyyy-mm-dd") - is it empty? This works for me – Dave – 2014-08-06T10:15:01.227

1No, it is not empty. Still the same format with the same value appears. For example, if A1 cell contains 30.10.2010, in cell B1 where I typed the formula, 30.10.2010 has come up again. I will update with the image. – Mawia HL – 2014-08-06T10:18:14.050

1When a date is initially entered in a form that matches the cell's date format it will be translated into a floating-point number -- days and fractions of a day. If you enter a format that doesn't match the cell's date format, or the cell doesn't have a date format set, then the data is stored as character data and will not auto-convert when you set/change the date format. – Daniel R Hicks – 2014-08-06T11:57:29.520

Answers

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?

Dave

Posted 2014-08-06T09:01:44.150

Reputation: 24 199

I just changed text function to substitute and used / and used text function again and it is working fine..Thanks you opened my eyes. – Mawia HL – 2014-08-06T10:27:49.013

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.

RCampello

Posted 2014-08-06T09:01:44.150

Reputation: 21

Why this format isn't available for the United States locale is beyond me... – Jarrett Barnett – 2016-11-22T22:04:18.777

-1

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.

C Law

Posted 2014-08-06T09:01:44.150

Reputation: 1

1So you're going to have the user change the region and language options for the entire OS just to make Excel happy? That is your suggestion? – Wes Sayeed – 2014-09-15T20:58:54.757