phpmyadmin Question posted in
The official documentation can be found here.

I need to change format of a date column from D-M-Y to Y-M-D in excel – Phpmyadmin

I was trying to import a CSV file to my phpmyadmin. Then my date field had values as 0000-00-00 since both date formats were different. I changed the CSV date-format to the format in the database. I have 5000+ data in my CSV. but only a few dates change to the format I chose. Remaining still in the old format

I went to Format cells, selected date, changed to Uk and selected the desired date format. But only a few dates change to the format I chose. Remaining still in the old format

Tags:

2

Answers


  1. 0

    Starting with data like:

    enter image description here

    Select the cells you wish to convert and run this:

    Sub FixDate()
    Dim s As String, d As String, rng As Range, cell As Range
    Dim dt As Date

    d = "-"

    For Each cell In Selection
        s = cell.Text
        If InStr(s, d) > 0 Then
            arr = Split(s, d)
            dt = DateSerial(arr(2), arr(1), arr(0))
            cell.Clear
            cell.Value = dt
            cell.NumberFormat = "dd-mm-yyy"
        End If
    Next cell
End Sub

    to produce:

    enter image description here

    Login or Signup to reply.
  2. 0

    You can only change the date format in Excel once Excel recognizes the cell contents as dates. If this doesn’t happen automatically, you can force it with the Text to columns function. After the dates have been recognized, you can use any date format you like.

    Select the column with the unrecognised dates, and go to Data tab, click Text to columns.
    Select Delimited, Next, Untick everything, Next, Select Date and from the dropdown select the order in which the dates currently are in your column. Hit Finish.
    TextToColumns

    In my example dates are in yyyy-dd-mm format.

    This is the fastest way I know of in Excel to make it recognize dates in any order.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search