How to calculate days:Hours:Minutes:

[Dana DeLouis]

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

 

[Kurewe]

See my response to myself at the bottom. Too many sub threads

 

[Kurewe]

See my response to myself at the bottom. Too many sub threads

 

[Kurewe]

See my response to myself at the bottom. Too many sub threads

 

[Ron Rosenfeld]

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

 

[Alan]

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.

 

[Kurewe]

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.

 

[Myrna Larson]

You've nailed it, Ron! I didn't realize he had two sets of data, supposedly
differing only WRT leading 0's.

 

[Myrna Larson]

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

 

[Hood]

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

 

[Bob Phillips]

Is this referring back to the post from July last year?