an earlier post

[Guest]

Hello Everyone,

(this is a repost, the first time I sent it in…it didn’t post??)
I have a question about an earlier post in the worksheet functions group
from 12/09/04. There was a question about converting numbers to time without
putting in the colons. The answer was a referral to Chip Pearson’s site. He
posted some code that converts any 4 numbers to time ie. 1234 to 12:34.
I want to know if it will work if I name a range due to the fact that my
cells are kind of scattered over the sheet and there are a lot of them. For
example this:

A5:A14,B5:B14,E5:E14,F5:F14 (many more than this)

I also want to know how I can make it work for the 6 digit format ie. 123456
to 12:34:56. Thanks for your help.

Rob

 

[Bob Phillips]

Don't know about Chip's code, but this works on a named range

Sub ChangeTime()
Dim cell As Range
Dim nHours As Long
Dim nMinutes As Long
Dim nSeconds As Long

For Each cell In Range("rng")
If IsNumeric(cell.Value) And cell.Value <> "" Then
With cell
nHours = .Value \ 10000
nMinutes = (.Value - nHours * 10000) \ 100
nSeconds = .Value Mod 100
.Value = TimeSerial(nHours, nMinutes, nSeconds)
End With
End If
Next cell

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)