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?

Try using **.Text** and not **.Value** when you retrieve cell contents. — Gary's Student, Aug 06 '14 at 12:47
@Gary'sStudent `.Text` can be unreliable, as it will return garbage `#####` when the column is not wide enough to display the date, etc. — Jean-François Corbett, Aug 06 '14 at 12:59
@Jean-FrançoisCorbett _____________You are correct!_________I will amend my answer. — Gary's Student, Aug 06 '14 at 13:54

bouvierr · Answer 1 · 2014-08-06T12:53:48.297

3

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")

edited Aug 06 '14 at 12:53

answered Aug 06 '14 at 12:47

bouvierr

3,563
3
27
32

+ 1 Yup that would be the right way :) Also OP needs to use `&` instead of `+` – Siddharth Rout Aug 06 '14 at 12:49
@bouvierr - I noticed now that it's converting regular numbers into dates also... how can I check the cell format? – Bramat Aug 06 '14 at 13:14
1

@Bramat: That depends on what you need. you can use `IsDate(x)` to check if it's a date or a string that **looks** like a date. You can also use `VarType(x) = vbDate` to check if a value is **stored** as a date. Check this [answer](http://stackoverflow.com/questions/4338025/isdate-function-returns-unexpected-results) for details. – bouvierr Aug 06 '14 at 21:57

score 0 · Answer 2 · edited Aug 06 '14 at 12:56

0

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.

edited Aug 06 '14 at 12:56

Jean-François Corbett

37,420
30
139
188

answered Aug 06 '14 at 12:56

Tom

9,725
3
31
48

1

Why resort to Excel's `TEXT` worksheetfunction when VBA's native `Format` does the job? – Jean-François Corbett Aug 06 '14 at 12:57
Because in Excel the Text worksheetfunction returns as a text variable instead of a date variable (Like OP requested). However for some reason in VBA it is returning it as a date variable still. My mistake – Tom Aug 06 '14 at 13:08

Print dates in proper format

2 Answers2

Linked