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, 2014 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, 2014 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, 2014 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, 2014 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, 2014 at 10:18

3 Answers 3

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, 2014 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... Nov 22, 2016 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, 2014 at 20:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .