If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#41
|
|||
|
|||
(THANK YOU ALL) How to calculate days:Hours:Minutes:
On Sun, 1 Aug 2004 19:19:03 -0700, Kurewe
wrote: I'm confused as to why and can only surmise that the added 0's somehow change the value Adding 0's doesn't change any values. However, changing a 0 to a 7 certainly will. Your two columns of data are not the same! That's why the averages are different. Here are the differences: 00 Days, 00 Hr, 07Min 0 Days, 0 Hr, 3Min 00 Days, 04 Hr, 11Min 7 Days, 4 Hr, 11Min If you correct this, then you will get the same result from both columns. Good luck! --ron |
#42
|
|||
|
|||
How to calculate days:Hours:Minutes:
"Dana DeLouis" wrote in message
... Just to share... Val has the same "issue" as CDbl with the Single letters D d E & e." However it didn't look like that was likely in this case. Just something to keep in mind. :) Sub Demo() Dim s1 As String Dim s2 As String s1 = "123D3" s2 = "123E3" Debug.Print Val(s1) Debug.Print CDbl(s1) Debug.Print Val(s2) Debug.Print CDbl(s2) End Sub Returns: 123000 123000 123000 123000 I can understand why VAL("123E3") = 123,000 but VAL("123D3") ?? I know the answer will be obvious, but..... Alan. |
#43
|
|||
|
|||
(THANK YOU ALL) How to calculate days:Hours:Minutes:
Yikes!!
Now I feel like an idiot... LOL I didn't even bother to check to make sure I entered everything correctly. Thank you for pointing that out. Now everything matches. "Ron Rosenfeld" wrote: On Sun, 1 Aug 2004 19:19:03 -0700, Kurewe wrote: I'm confused as to why and can only surmise that the added 0's somehow change the value Adding 0's doesn't change any values. However, changing a 0 to a 7 certainly will. Your two columns of data are not the same! That's why the averages are different. Here are the differences: 00 Days, 00 Hr, 07Min 0 Days, 0 Hr, 3Min 00 Days, 04 Hr, 11Min 7 Days, 4 Hr, 11Min If you correct this, then you will get the same result from both columns. Good luck! --ron |
#44
|
|||
|
|||
(THANK YOU ALL) How to calculate days:Hours:Minutes:
You've nailed it, Ron! I didn't realize he had two sets of data, supposedly
differing only WRT leading 0's. On Sun, 01 Aug 2004 23:03:56 -0400, Ron Rosenfeld wrote: On Sun, 1 Aug 2004 19:19:03 -0700, Kurewe wrote: I'm confused as to why and can only surmise that the added 0's somehow change the value Adding 0's doesn't change any values. However, changing a 0 to a 7 certainly will. Your two columns of data are not the same! That's why the averages are different. Here are the differences: 00 Days, 00 Hr, 07Min 0 Days, 0 Hr, 3Min 00 Days, 04 Hr, 11Min 7 Days, 4 Hr, 11Min If you correct this, then you will get the same result from both columns. Good luck! --ron |
#45
|
|||
|
|||
How to calculate days:Hours:Minutes:
Just to share... Val has the same "issue" as CDbl with the Single letters D
d E & e." Hmmm... With XL 2002, in the immediate window, ? Val("123D") prints 123. I don't get an error. Ditto with "123E" Here's the scoop WRT D and E (Dana, you probably know this!): This is a hold-over from the "Good Old Days": you enter a number in scientific format using 1.23E03. This syntax makes it SINGLE precision. OTOH, when they added double precision numbers, you got the option of replacing the E with D, i.e. if you write 1.23D03, it is interpreted and converted to DOUBLE precision rather than single. So if you have a D or E followed by 1-3 *digits*, VBA interprets the text as a number in scientific notation. Without the trailing digits, it just stops at the D or E. In my code, I required the text be "like" "*Days,*Hrs,*Min" (also checked for "Day," instead of "Days"). If that test fails, the routine returns 0. So a typo like "5D1ys" won't slip through as 50 days. It will be returned as 0. If there is a possiblity of syntax errors in the inputs, the OP can use the standalone ConvertToDecimal routine in an extra column to check for 0 results. Presumably there shouldn't be any... On Sun, 1 Aug 2004 20:58:09 -0400, "Dana DeLouis" wrote: Just to share... Val has the same "issue" as CDbl with the Single letters D d E & e." However it didn't look like that was likely in this case. Just something to keep in mind. :) Sub Demo() Dim s1 As String Dim s2 As String s1 = "123D3" s2 = "123E3" Debug.Print Val(s1) Debug.Print CDbl(s1) Debug.Print Val(s2) Debug.Print CDbl(s2) End Sub Returns: 123000 123000 123000 123000 HTH Dana DeLouis "Myrna Larson" wrote in message .. . And in this case, the "old" way has a definite advantage over CDbl, CLng, CInt, etc. It stops converting when it hits a character (like the D in Days) that can't be part of a number. CDbl would give a type-mismatch error. On Sun, 01 Aug 2004 20:15:32 -0400, Ron Rosenfeld wrote: On Sun, 1 Aug 2004 18:09:44 -0400, "Dana DeLouis" wrote: Val(v(0)) + Val(v(1)), Val(v(2)), 0) Neat! I purposely did not use Split, not knowing what version the OP had; however, I did not realize that Val would work on strings that started with numeric data. I need to remember that one. --ron |
#46
|
|||
|
|||
Norman
this has helped me immensely, most of this thread was completely over my head, funny that what I needed was actually this straightforward procedure. Cheers "Norman Harker" wrote: Hi Kurewe! I think that your best bet may be to use Data Text to Columns on your data. You'll need to set up 6 empty columns to the right of your data. Then select your data Data Text to columns Delimited Use a space as the delimiter Add Other M as an additional delimiter Next Finish That gives you your numerics in columns A C and E Thereafter, it's easy to derive a formula to do what you want. Send a sample workbook if you like and I'll do it. On limited test data it appears to work OK. It certainly beats the complicated formulas although maybe a subroutine will be a better solution. -- Regards Norman Harker MVP (Excel) Sydney, Australia |
#47
|
|||
|
|||
Is this referring back to the post from July last year?
"Hood" wrote in message ... Norman this has helped me immensely, most of this thread was completely over my head, funny that what I needed was actually this straightforward procedure. Cheers "Norman Harker" wrote: Hi Kurewe! I think that your best bet may be to use Data Text to Columns on your data. You'll need to set up 6 empty columns to the right of your data. Then select your data Data Text to columns Delimited Use a space as the delimiter Add Other M as an additional delimiter Next Finish That gives you your numerics in columns A C and E Thereafter, it's easy to derive a formula to do what you want. Send a sample workbook if you like and I'll do it. On limited test data it appears to work OK. It certainly beats the complicated formulas although maybe a subroutine will be a better solution. -- Regards Norman Harker MVP (Excel) Sydney, Australia |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to calculate time difference? | barton | General Discussion | 4 | July 13th, 2004 05:49 AM |
How to calculate time difference? | barton | General Discussion | 0 | July 12th, 2004 06:31 AM |
Visual Basic....Calculate Button | Don Guillett | Worksheet Functions | 3 | April 2nd, 2004 05:24 PM |