Formatting time from dd/mm/yyyy hh:mm:ss to yyyy.mm.dd hh:mm

Question

I received the following task: turn "dd/mm/yyyy hh:mm:ss" time to "yyyy.mm.dd hh:mm" format.

I was hoping that a simple Format(activecell.value, "yyyy.mm.dd hh:mm") would help, but. In some cases it works. However, in some special cases, it doesn't. When the day is between 01-12 (interchangeable with months!), then it messes up the days with months. (Probably because of US style date format.)

For example, original text string: 04/06/2013 09:00:00

Expected: 2013.06.04 09:00

Result: 2013.04.06 09:00 (day and month have been swapped)

I tried to overcome it by formatting the input, I gave it a "dd/mm/yyyy hh:mm:ss" custom format. Didn't help, it still swaps the day and the month.

So I have no ideas anymore, but regular expressions. Date format is always the same so just moving the parts of the string would be okay. However, I don't know how that works and google searching brings up confusing results.

Can anyone help me? Of course if there is a more elegant way to do it, that's also welcome.

How did the dates get into the worksheet? If it was badly imported, errors like this (mix between British and US dates) often occur. I would suggest to first format it correctly. One indicator I found from experience is that the dates aren't grouped together when you put a filter and open the filter for the dates. — Jerry
I'm not the one importing the data so I don't know how it gets done. I have to work with it like this, not much I can do about it sadly. — atira
Okay, did you try the filter thing I mentioned earlier? I'm not sure how to ask you so I get the response I'm looking for. Can you identify which dates are badly formatted? I'm suspecting that the 'good dates' are in dd/mm/yyyy hh:mm:ss format while the bad dates are in mm/dd/yyyy hh:mm:ss. What happens if you format all of them as mm/dd/yyyy hh:mm:ss? Do all the dates change, or only the good dates change? — Jerry
They are recognized as "general" by Excel. They are originally in dd/mm/yyyy, but some of them are recognized as mm/dd/yyyy. — atira

user2140261 user2140261 · Accepted Answer · 2013-04-22T12:42:03

Try using this Custom Format:

yyyy/mm/dd h:mm;@

To enter it, right click on the cell and Choose Format Cell:

Format Cell

Then under the Number Tab Select Custom from the listbox. And enter the provided format in the Type: textbox.

Example

If that doesn't work. Are you *****Absolutely***** Positive that 04/06/2013 09:00:00 IS April 6, 2013 and NOT June 4, 2013??

If this still doesn't work and you have verified that dates are correct.

Sub ChangeDateFormat()

Application.ScreenUpdating = False

Dim CurrentCell As Range
Dim LastRow As Long
Dim RegEx As Object
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Global = True

LastRow = Range("A" & Rows.Count).End(xlUp).Row 'Get The Last Row in Column Change A as Needed

For Each CurrentCell In Range("A1:A" & LastRow) ' Loop through all cells. Change Column as needed

    If InStr(CurrentCell.Value, "/") <> 0 Then 'To make sure only convert non converted ones

        RegEx.Pattern = "(\d{2})/(\d{2})/(\d{4}) (\d{2}):(\d{2}):(\d{2})" ' Seperate all parts of imported Data into groups
        CurrentCell.Value = RegEx.Replace(CurrentCell.Value, "$3.$2.$1 $4:$5") ' Change order of groups and place into cell.

    End If

Next

Application.ScreenUpdating = True

End Sub

*****NOTE: ***** This will only work if ALL date values are of dd/mm/yyyy hh:mm:ss if they are not you will have to add some Error Handling and possibly slightly modify the code as it WILL cause problems.

This will also work on dates that are inside of other text. If the value of A1 is

This is a test 04/06/2013 09:00:00 Lets see what happens

Then the new value will be

This is a test 2013.06.04 09:00 Lets see what happens

Formatting time from dd/mm/yyyy hh:mm:ss to yyyy.mm.dd hh:mm

3 Answers