1

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

10
  • 2
    Any problem with just setting the format on the cell?
    – user
    Aug 6 '14 at 9:04
  • 1
    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 6 '14 at 9:07
  • 1
    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 6 '14 at 9:49
  • 2
    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 6 '14 at 10:15
  • 1
    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 6 '14 at 10:18
2

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?

1
  • 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 6 '14 at 10:27
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.

1
  • Why this format isn't available for the United States locale is beyond me...
    – JBB
    Nov 22 '16 at 22:04
-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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.