Print dates in proper format

Question

I have an excel workbook with many dates.

• On the worksheet they are in a format of "mm/dd/yy hh:mm"
• What I see in the white box above the sheet is in the format of "dd/mm/yy hh:mm"

I need to print the worksheets into text files, but when I do so, it's printing in the format of dd/mm/yy hh:mm, Instead of mm/dd/yy hh:mm.

Here's the code:

         For i = 1 To LastRow
            For j = 1 To LastCol
                If j = LastCol Then
                    DataLine = DataLine + Trim(Cells(i, j).Value)
                Else
                    DataLine = DataLine + Trim(Cells(i, j).Value) + vbTab
                End If
            Next j

            If (DataLine <> "") Then
                Print #FileNum, DataLine
                DataLine = ""
            End If
        Next i

Does any one has an idea how to print the sheets in the format I need?

Answer 1

You can use the Format function to customize the text formatting of dates.

I escaped the forward slash / because otherwise it is interpreted as the date separator and "The actual character used as the date separator in formatted output is determined by your system settings" (from documentation). With escaping, it should always output a / character.

Format(Cells(1, 1).Value, "MM\/dd\/yy hh:mm")

Answer 2

You could use Format(Cells(i,j), "mm/dd/yy hh:mm") to do this

Or what might work better is you could use

Cells(i,j) = Application.WorksheetFunction.Text(Now(), "dd/mm/yy hh:mm")

This would return a text variable instead of datetime.