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

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)


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

Hi there!

A simpler formula to use would be:

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

Hope this helps!

Richard


(E-Mail Removed) wrote:
> 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

He asked for a macro :-)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"RichardSchollar" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi there!
>
> A simpler formula to use would be:
>
> =TEXT(A1,"00\:00")+0
>
> Hope this helps!
>
> Richard
>
>
> (E-Mail Removed) wrote:
>> 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
>

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.

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)


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>

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

James

Bob Phillips wrote:
> 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)
>
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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 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)
> > / 24

Cheers and thanks again.

James

(E-Mail Removed) wrote:
> Thanks Bob - now I understand the offset. Brilliant - thank you again.
>
> James
>
> Bob Phillips wrote:
> > 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)
> >
> >
> > <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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.
> > >

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

--
Regards,
Tom Ogilvy


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)
>> > / 24
>
> Cheers and thanks again.
>
> James
>
> (E-Mail Removed) wrote:
>> Thanks Bob - now I understand the offset. Brilliant - thank you again.
>>
>> James
>>
>> Bob Phillips wrote:
>> > 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)
>> >
>> >
>> > <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> > > 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.
>> > >
>

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


Tom Ogilvy wrote:
> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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)
> >> > / 24
> >
> > Cheers and thanks again.
> >
> > James
> >
> > (E-Mail Removed) wrote:
> >> Thanks Bob - now I understand the offset. Brilliant - thank you again.
> >>
> >> James
> >>
> >> Bob Phillips wrote:
> >> > 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)
> >> >
> >> >
> >> > <(E-Mail Removed)> wrote in message
> >> > news:(E-Mail Removed)...
> >> > > 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.
> >> > >
> >