A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to calculate days:Hours:Minutes:



 
 
Thread Tools Display Modes
  #41  
Old August 2nd, 2004, 04:03 AM
Ron Rosenfeld
external usenet poster
 
Posts: n/a
Default (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  
Old August 2nd, 2004, 04:14 AM
Alan
external usenet poster
 
Posts: n/a
Default 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  
Old August 2nd, 2004, 04:17 AM
Kurewe
external usenet poster
 
Posts: n/a
Default (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  
Old August 2nd, 2004, 04:20 AM
Myrna Larson
external usenet poster
 
Posts: n/a
Default (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  
Old August 2nd, 2004, 04:36 AM
Myrna Larson
external usenet poster
 
Posts: n/a
Default 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  
Old January 17th, 2005, 11:11 AM
Hood
external usenet poster
 
Posts: n/a
Default

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  
Old January 17th, 2005, 12:11 PM
Bob Phillips
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 09:01 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.