working with cells and text
 

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.

working with cells and text
 

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.

working with cells and text
 

Job,

Select the cells with the times, and run the macro below.

HTH,
Bernie
MS Excel MVP

Sub test()
Dim mycell As Range
For Each mycell In Selection
mycell.Value = "'" & Format(mycell.Value, "h:mm")
Next mycell
End Sub


"Job" wrote in message ...
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.

working with cells and text
 

Brilliant, thank you both. I ended up using Jims code as I didn't want the
"'" in the cell.

Cheers,

Job


"Job" wrote in message
...
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.

working with cells and text
 

Ok, I have a small snag. The value in the Cell is 83:15 which is 83 min
and 15 seconds. It's format is actually 83:15:00 as opposed to the others
that are like 15:51. The values that are in this column are just minutes
and seconds. Excel must be converting them as they are put into Excel,
however, I only recieve the file after it has been saved as an .xls.

Any thoughts?



"Job" wrote in message
...
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.

working with cells and text
 

I just noticed that in my first post I said it was hours and minutes...it's
actually minutes and seconds...

"Job" wrote in message
...
Ok, I have a small snag. The value in the Cell is 83:15 which is 83 min
and 15 seconds. It's format is actually 83:15:00 as opposed to the others
that are like 15:51. The values that are in this column are just minutes
and seconds. Excel must be converting them as they are put into Excel,
however, I only recieve the file after it has been saved as an .xls.

Any thoughts?



"Job" wrote in message
...
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.

working with cells and text
 

Play with this and see if you can get what you want...

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) < "=" And
IsNumeric(rngCurrent.Value) Then
If rngCurrent.Value < 1 And rngCurrent.Value 0 Then
strTime = CStr(Format(rngCurrent.Value, "mm:ss"))
rngCurrent.NumberFormat = "@"
rngCurrent.Value = strTime
ElseIf rngCurrent.Value 0 Then
strTime = CStr(Format(rngCurrent.Value / 60, "h:mm:ss"))
rngCurrent.NumberFormat = "@"
rngCurrent.Value = strTime
End If
End If
Next
Application.Calculation = xlCalculationAutomatic
End If
End Sub

--
HTH...

Jim Thomlinson


"Job" wrote:

Ok, I have a small snag. The value in the Cell is 83:15 which is 83 min
and 15 seconds. It's format is actually 83:15:00 as opposed to the others
that are like 15:51. The values that are in this column are just minutes
and seconds. Excel must be converting them as they are put into Excel,
however, I only recieve the file after it has been saved as an .xls.

Any thoughts?



"Job" wrote in message
...
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.

working with cells and text
 

Great. Thanks Jim. Looks like I can tweak this to fit my needs. Your help
is much appreciated.
"Jim Thomlinson" wrote in message
...
Play with this and see if you can get what you want...

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) < "=" And
IsNumeric(rngCurrent.Value) Then
If rngCurrent.Value < 1 And rngCurrent.Value 0 Then
strTime = CStr(Format(rngCurrent.Value, "mm:ss"))
rngCurrent.NumberFormat = "@"
rngCurrent.Value = strTime
ElseIf rngCurrent.Value 0 Then
strTime = CStr(Format(rngCurrent.Value / 60,
"h:mm:ss"))
rngCurrent.NumberFormat = "@"
rngCurrent.Value = strTime
End If
End If
Next
Application.Calculation = xlCalculationAutomatic
End If
End Sub

--
HTH...

Jim Thomlinson


"Job" wrote:

Ok, I have a small snag. The value in the Cell is 83:15 which is 83 min
and 15 seconds. It's format is actually 83:15:00 as opposed to the others
that are like 15:51. The values that are in this column are just minutes
and seconds. Excel must be converting them as they are put into Excel,
however, I only recieve the file after it has been saved as an .xls.

Any thoughts?



"Job" wrote in message
...
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.