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.

enter image description here

If I manually type the date, everything is OK. Format setting seems OK. — Mawia HL, Aug 06 '14 at 09:06
But 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? — user, Aug 06 '14 at 09:07
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, Aug 06 '14 at 09:09
I 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, Aug 06 '14 at 09:49
=Text(A1,"yyyy-mm-dd"), does this work? And this is what I am asking. — Mawia HL, Aug 06 '14 at 09:57
@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, Aug 06 '14 at 10:05
You 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, Aug 06 '14 at 10:15
No, 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, Aug 06 '14 at 10:18
When 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, Aug 06 '14 at 11:57

Dave · Accepted Answer · 2017-03-17T08:14:56.897

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?

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, Aug 06 '14 at 10:27

score 2 · Answer 2 · edited Mar 17 '17 at 08:16

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.

edited Mar 17 '17 at 08:16

Dave

25,297
10
57
69

answered Feb 28 '15 at 14:29

RCampello

21
2

Why this format isn't available for the United States locale is beyond me... – Jarrett Barnett Nov 22 '16 at 22:04

score -1 · Answer 3 · edited Apr 28 '15 at 08:42

-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.

edited Apr 28 '15 at 08:42

Dave

25,297
10
57
69

answered Sep 15 '14 at 20:47

C Law

1

1

So 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 Sep 15 '14 at 20:58

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

3 Answers3

Linked