Forcing Date Format in Excel

Question

I have a column containing dates in the right format:

I am trying to first change the "." to "/" which works fine which the following code, but it automatically modifies the date format and recognizes the day as month if it's under 12.

'2) Replace "." by "/"
    'Range("C:C").NumberFormat = "@" ' I tried with AND without this line...no difference
    'Range("C:C").NumberFormat = "dd.mm.yyyy hh:mm:ss" ' if I add this then only "." starting from 13th of January are reaplced by "/"
    'Range("C:C").NumberFormat = "dd/mm/yyyy hh:mm:ss" ' no differences at all neither
    Columns("C:C").Select
    Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

So annoying...any ideas how to fix this ?

Answer 1

This code takes C2:C25 in Sheet1, converts it to an in-memory array, traverses that array and converts all values to actual dates, and then dumps the converted values to D2:D25 on Sheet1, applies the desired NumberFormat in column D, and then resizes the column to fit.

The result is dates properly treated as dates, custom-formatted:

Public Sub ConvertToDate()

    Dim target As Variant
    target = ToArray(Sheet1.Range("C2:C25")) 'todo: adjust to desired range

    Dim i As Long
    For i = LBound(target) To UBound(target)
        target(i) = ToDate(target(i))
    Next

    'here you'd probably dump the result in C2:
    Sheet1.Range("D:D").NumberFormat = "dd.MM.yyyy hh:mm:ss"
    Sheet1.Range("D2").Resize(UBound(target), 1).value = Application.WorksheetFunction.Transpose(target)
    Sheet1.Range("D:D").EntireColumn.AutoFit

End Sub

Private Function ToDate(ByVal value As String) As Date

    ' make sure our assumptions are correct before going any further:
    Debug.Assert value Like "##.##.#### ##:##:##"

    Dim datetimeParts As Variant
    datetimeParts = Strings.Split(value, " ")

    Dim dateParts As Variant
    dateParts = Strings.Split(datetimeParts(0), ".")

    Dim datePart As Date
    datePart = DateTime.DateSerial(dateParts(2), dateParts(1), dateParts(0))

    Dim result As Date
    result = CDate((CStr(datePart) & " " & datetimeParts(1)))

    ToDate = result

End Function

The ToArray helper function is this one (adapted from this post):

Private Function ToArray(ByVal target As Range) As Variant
    Select Case True
        Case target.Rows.Count = 1
            'horizontal 1D range
            ToArray = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(target.value))
        Case target.Columns.Count = 1
            'vertical 1D range
            ToArray = Application.WorksheetFunction.Transpose(target.value)
        Case Else
            '2D array: let Excel to the conversion itself
            ToArray = target.value
    End Select
End Function

You should be able to easily adapt this code to work with your worksheet and data.

Answer 2

This should suffice for what you need. I don't care for the .Range("C:C") here, but this should be fine as long as column C only ever has this data in it. The problem with using .Range("C:C") is that it will always modify and load the entire column which can reduce performance. If I have a chance, ill edit the code to use a more elegant solution, I just wanted to get a working answer up first to get you started.

In any event, here's the code:

Sub FixDateFormatting()
    Dim ArrayDates() As Variant

    ' Load all the dates into an array for modification
    ArrayDates = ThisWorkbook.Sheets(1).Range("C:C").Value

    ' Edit the format of the destination to be text based. This will prevent Excel from assuming format
    ' Note: This must be done after the values are put into the array, otherwise you could load values in the
    ' wrong format.
    ThisWorkbook.Sheets(1).Range("C:C").NumberFormat = "@"

    Dim i As Long
    ' Loop through the array and properly format all of the data
    For i = LBound(ArrayDates, 1) To UBound(ArrayDates, 1)
        ArrayDates(i, 1) = Format(CStr(Replace(ArrayDates(i, 1), ".", "/")), "dd/mm/yyyy hh:mm:ss")
    Next

    ' Output the modified data
    ThisWorkbook.Sheets(1).Range("C:C").Value = ArrayDates
End Sub

Replace ThisWorkbook.Sheets(1) with an appropriate reference to the sheet you are modifying. You don't have to if the target sheet is the first sheet within the workbook running the code.

This should be much better than a loop. The only downside to this approach is that in order to retrieve the values from these cells and perform operations on them (using the dd-mm-yyyy format) is that you have to retrieve and manipulate these values in another array. If you try using excel formulas on these values you will not get the expected results. This is unavoidable (at least to my knowledge) when you are using a non-standard date format.

Let me know if you have any questions.

Take care,

Brandon

EDIT:

Here's a slightly more elegant solution that should have a slight performance increase. I made it (hopefully) easier to set the correct target worksheet. I also have resized the range to only include the necessary number of rows. See below:

Sub FixDateFormatting()
    Dim TargetSheet As Worksheet
    ' Set the correct target sheet here:
    Set TargetSheet = ThisWorkbook.Sheets(1)

    Dim LastColRow As Long
    ' Store the absolute last row within a long variable for later use
    LastColRow = TargetSheet.Range("C1048576").End(xlUp).Row

    Dim TargetRange As Range
    ' Assumes your data starts in cell 2 (has a header row). Change the 2 as needed.
    Set TargetRange = TargetSheet.Range("C2:C" & LastColRow)

    Dim ArrayDates() As Variant

    ' Load all the dates into an array for modification
    ArrayDates = TargetRange.Value

    ' Edit the format of the destination to be text based. This will prevent Excel from assuming format
    ' Note: This must be done after the values are put into the array, otherwise you could load values in the
    ' wrong format.
    TargetRange.NumberFormat = "@"

    Dim i As Long
    ' Loop through the array and properly format all of the data
    For i = LBound(ArrayDates, 1) To UBound(ArrayDates, 1)
        ArrayDates(i, 1) = Format(CStr(Replace(ArrayDates(i, 1), ".", "/")), "dd/mm/yyyy hh:mm:ss")
    Next

    ' Output the modified data
    TargetRange.Value = ArrayDates
End Sub

EDIT (Again):

This last solution is far more elegant and preserves the "DATE" format. You can then edit the cell as needed. This uses a UDF (user defined function). You can just type the function with the date to be fixed as the target. It will output a date that you can then modify to the formatting you need:

Public Function FixDateFormat(InputDate As String) As Date
    ' This will ensure that the string being input is appropriate for this function
    ' Modify the pattern as needed.

    If InputDate Like "##.##.#### ##:##:##" Then
        Dim DateTime As Variant
        DateTime = Split(InputDate, " ")

        Dim DateInfo As Variant
        DateInfo = Split(DateTime(0), ".")

        Dim HolderString As String
        HolderString = Format(DateInfo(1), "00") & "/" & Format(DateInfo(0), "00") & "/" & Format(DateInfo(2), "0000") & " " & DateTime(1)

        Debug.Print HolderString

        Dim OutputDate As Date
        OutputDate = CDate(HolderString)

        FixDateFormat = OutputDate
    Else
        ' Comment out this line to return a "#VALUE" error instead
        FixDateFormat = vbNullDate
        Exit Function
    End If
End Function

Answer 3

Without having to manually modify regional settings, you must use a loop.

LastRow = Cells(Rows.Count, "C").End(xlUp).Row
Set r = Range(Cells(1, "C"), Cells(LastRow, "C"))
t = r.Value
For i = 1 To UBound(t)
 t(i, 1) = Replace(t(i, 1), ".", "/")
Next
r.NumberFormat = ""
r.FormulaLocal = t

Answer 4

What about something like this

Sub Makro3()

Columns("C:C").Select

Selection.NumberFormat = "dd\/mm\/yyyy hh:mm:ss"
End Sub

Added hh:mm:ss

Answer 5

If you don't mind looping through your data you can do the following:

Sub Test()

Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("SheetName")

For Each Cell In ws.Range("C2:C" & ws.Cells(ws.Rows.Count, "C").End(xlUp).Row)
    Cell.Value = CDate(Replace(Cell.Value, ".", "/"))
Next Cell

End Sub

Or, if you want you can use a helper column and use the following formula:

=TEXT(DATE(MID(C2,SEARCH(".",C2,SEARCH(".",C2,1)+1)+1,4),MID(C2,SEARCH(".",C2,1)+1,2),MID(C2,1,2))+TIMEVALUE(RIGHT(C2,8)),"dd/mm/yyyy hh:mm:ss")

Answer 6

There are a variety of ways of dealing with this, and the most obvious would be to adjust your NumberFormat property. Try recording some macros with various formats to see how it could work for you. In your case, the code could be something like:

Sheet1.Columns("C:C").NumberFormat = "mm/dd/yyyy"