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
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?
No comments:
Post a Comment