printing time literally

[Bart van den Burg]

Hi

I'm printing data from an excel sheet to a text file, and some of
these cells contain Time's. However, printing the cell results in a number
between 0 and 1 rather than the exact string, eg "12:30" or "4:00". How can
I print it like that anyway?

Thanks
Bart

 

[GJones]

Hi Bart;

You could try converting the values in the time based
cells to strings. Then when you print them to a text file
they will not show up as numbers.

Thanks,

Greg

 

[Doug Glancy]

Bart,

This will convert the value of the time to a string, held in the variable.
I then had to format the cell as text and then set the cell's value to the
string. After printing I formatted back to original format and reset the
value again - otherwise it didn't take.

Sub test()
Dim date_str As String
Dim cell_format As String

With Worksheets("Sheet1").Range("A1")
cell_format = .NumberFormat
date_str = CStr(Hour(.Value) & ":" & Minute(.Value))
.NumberFormat = "@"
.Value = date_str
'print to text file here
.NumberFormat = cell_format
.Value = date_str
End With
End Sub