Dim cell As Range

For Each cell In Selection.Columns(1).Cells
With cell
.Offset(0, 1).Value = ((.Value \ 100) + (.Value - (.Value \ 100)
* 100) / 60) / 24
.Offset(0, 1).NumberFormat = "h:mm AM/PM"
End With
Next cell


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
ups.com...
Hi all. I'm new to excel macro's so please forgive my ignorance.

I have inherited a spreadsheet where times have been entered as whole
numbers
ie) 2155 instead of 21:55 OR 32 instead of 00:32

Currently I am creating a new column alongside the one with data in and
using the following formula to convert these to a normal time format

=TIME(((IF(C1399,ROUNDDOWN(C13,-2),0))/100),(C13-(IF(C1399,ROUNDDOWN(C13,-2),0))),0)

Of course this only works for whatever cell I am working on at the time
eg in this case D13. <copy formula highlight cells <paste

What I want to do is create a macro that allows me to use this formula
on a selection of cells, changing the values of these cells without
having to create a new column and without having to do each cell
individually or <copy formula highlight cells <paste

eg. If I have the following values
A1: 2155
A2: 32
A3: 923
A4: 1512
I need a macro to incorporate the above formula to convert my values to
A1: 21:55
A2: 00:32
A3: 09:23
A4: 15:12

Thanks in advance

TBD