Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
complex time formula
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex color fill conditions- if statements or complex formula? | Excel Discussion (Misc queries) | |||
Complex IF formula w/ time | Excel Worksheet Functions | |||
convert from percentage of time to time using complex formula in . | Excel Worksheet Functions | |||
Complex Time Window / date based calculation | Excel Worksheet Functions | |||
Still adding to Complex Macro...1 step at a time. | Excel Programming |