complex time formula
 

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

complex time formula
 

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

complex time formula
 

Hi there!

A simpler formula to use would be:

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

Hope this helps!

Richard


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(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

complex time formula
 

He asked for a macro :-)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"RichardSchollar" wrote in message
oups.com...
Hi there!

A simpler formula to use would be:

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

Hope this helps!

Richard


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(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

complex time formula
 

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.

complex time formula
 

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)


wrote in message
ps.com...
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.

complex time formula
 

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)


wrote in message
ps.com...
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.

complex time formula
 

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

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)


wrote in message
ps.com...
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.

complex time formula
 

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


wrote in message
oups.com...
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

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)


wrote in message
ps.com...
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.

complex time formula
 

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


wrote in message
oups.com...
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

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)


wrote in message
ps.com...
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.