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

 

[Guest]

Myrna, Daniel, Ron and Dana:

First of all, I want to thank you all for your patience and your assistance. I have learned some new things from each of you. I marked each of you suggestions as answering the question. They all Work.

I will likely be using Myrna's suggestion. The reason being, that her suggestion allows for multiple ranges (which includes ranges from other sheets within the workbook). This will help me in the long run, as the project evolves.

With regards to this last hurdle... Myrna, I believe you're right with regards to the "23 Days, 23 Hrs, 19 Min" being the proper result. I'm confused as to why and can only surmise that the added 0's somehow change the value. Based on my new found interest in VBA and

I have tested each person's suggestion with both the data sets below. Each of them provide the exact same answers as shown below.

If it helps to understand where I am coming from, I have uploaded my test workbook to my site. All four suggestions are there, with the data that I used below. I've also included my email address in the workbook if you would prefer to continue via email.
http://www.kurewe.com/book1.xls

23 Days, 17 Hrs, 5 Min 23 Days, 23 Hrs, 19 Min

With the 0's Without the 0's

66 Days, 23 Hr, 11Min 66 Days, 23 Hr, 11Min
00 Days, 00 Hr, 07Min 0 Days, 0 Hr, 3Min
44 Days, 23 Hr, 06Min 44 Days, 23 Hr, 6Min
10 Days, 19 Hr, 51Min 10 Days, 19 Hr, 51Min
12 Days, 01 Hr, 17Min 12 Days, 1 Hr, 17Min
33 Days, 01 Hr, 21Min 33 Days, 1 Hr, 21Min
60 Days, 23 Hr, 04Min 60 Days, 23 Hr, 4Min
00 Days, 00 Hr, 07Min 0 Days, 0 Hr, 7Min
62 Days, 19 Hr, 19Min 62 Days, 19 Hr, 19Min
01 Days, 20 Hr, 26Min 1 Days, 20 Hr, 26Min
04 Days, 04 Hr, 52Min 4 Days, 4 Hr, 52Min
77 Days, 16 Hr, 58Min 77 Days, 16 Hr, 58Min
13 Days, 19 Hr, 21Min 13 Days, 19 Hr, 21Min
24 Days, 01 Hr, 04Min 24 Days, 1 Hr, 4Min
24 Days, 01 Hr, 06Min 24 Days, 1 Hr, 6Min
48 Days, 19 Hr, 40Min 48 Days, 19 Hr, 40Min
01 Days, 02 Hr, 35Min 1 Days, 2 Hr, 35Min
00 Days, 00 Hr, 05Min 0 Days, 0 Hr, 5Min
00 Days, 00 Hr, 07Min 0 Days, 0 Hr, 7Min
00 Days, 00 Hr, 13Min 0 Days, 0 Hr, 13Min
65 Days, 03 Hr, 31Min 65 Days, 3 Hr, 31Min
00 Days, 04 Hr, 11Min 7 Days, 4 Hr, 11Min
00 Days, 00 Hr, 10Min 0 Days, 0 Hr, 10Min
00 Days, 19 Hr, 20Min 0 Days, 19 Hr, 20Min
00 Days, 00 Hr, 28Min 0 Days, 0 Hr, 28Min
55 Days, 18 Hr, 54Min 55 Days, 18 Hr, 54Min
31 Days, 01 Hr, 14Min 31 Days, 1 Hr, 14Min

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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?