complex time formula

[nsstick]

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(C13>99,ROUNDDOWN(C13,-2),0))/100),(C13-(IF(C13>99,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

 

[Bob Phillips]

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)

 

[RichardSchollar]

Hi there!

A simpler formula to use would be:

=TEXT(A1,"00\:00")+0

Hope this helps!

Richard

 

[Bob Phillips]

He asked for a macro

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[nsstick]

Thanks Bob for the macro. It has been interesting working through
what you have done with it as this is the first time I am working with
VBA.

Is it possible to get the macro to replace the existing values instead
of starting a new column?

Cheers - James

PS Richard thanks also for the simpler formula (I've just woken up -
West coast of Australia). I'm about to work through what you have done
with that as it does seem a lot simpler than what I was using.

 

[Bob Phillips]

Dim cell As Range

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



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[nsstick]

Thanks Bob - now I understand the offset. Brilliant - thank you again.

James

 

[nsstick]

Bob et al,

A couple more questions to help my ignorance:

1.) In the spreadsheet I have columns which include one of two
possible text strings: BKD or ANR. I need to leave them without
changing them. The macro you have helped me with gets stuck when it
encounters text. How do I get the macro to differentiate between text
and integers? And how do I get it to ignore the text?

2.) what is the difference between using the '\' and '/' in this line:
..Value = ((.Value \ 100) + (.Value - (.Value \ 100) * 100) / 60)
Cheers and thanks again.

James

 

[Tom Ogilvy]

Dim cell As Range

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

/ this is normal division
\ this is integer division

demo'd from the immediate window:


? 7 / 3
2.33333333333333
? 7 \ 3
2

 

[nsstick]

Thanks Tom that helps hugely. I appreciate you taking time to help us
newbies especially with something as simple as the difference in
division.

Cheers

James