Here is a procedure to change time values to text. The important thing to
note is that there is no IsTime function. Any value between 0 and .9999 could
be displayed as a time. So this procedure takes those values and converts
them to a corresponding time string (whether they are really a time or not).
You can use it by highlighting the cells you want to convert (one cell, a
group of cells, entire columns or rows) and executing the procedure.

Private Sub Convert()
Dim rngCurrent As Range
Dim rngToSearch As Range
Dim strTime As String

Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection)
If rngToSearch Is Nothing Then Set rngToSearch = ActiveCell
If Not rngToSearch Is Nothing Then
Application.Calculation = xlCalculationManual
For Each rngCurrent In rngToSearch
If Left(rngCurrent.Value, 1) < "=" Then
If rngCurrent.Value < 1 And rngCurrent.Value 0 Then
strTime = CStr(Format(rngCurrent.Value, "h:mm"))
rngCurrent.NumberFormat = "@"
rngCurrent.Value = strTime
End If
End If
Next
Application.Calculation = xlCalculationAutomatic
End If
End Sub
--
HTH...

Jim Thomlinson


"Job" wrote:

I have a number of cells that have duration of time values, i.e 6:30,5:28;
which are hours and minutes. I actually want these values but in text
format. They would look identical but wouldn't have the Date/Time stamp on
them. What I have done in the past is copy the column of data and paste
into notepad, then made the column a text format then pasted back from
notepad into Excel. This gives the desired result, however, there has to be
a better way of doing this via code or something.

Any help is appreciated.