Excel VBA rounding / time handling questions

[GSpline]

I am attempting to convert a variable number of seconds to a readout that
shows Hrs:Min:Sec. I have been trying to just convert the seconds to hours,
round the number of hours to the nearest (and lowest) whole number then
subtract the number of hours (in seconds) from the original number of seconds
and then do the same thing with the minutes field. The rounding does not do
what I am intending as it sometimes rounds up instead of down. I have tried
to look in my excel VBA online help but when the search results display & I
click on the item of interest it will not do anything, will not show the
requested help file. I tried to use the ROUNDDOWN function but I am told
that the Sub or Function is not defined. I checked in excel and I have the
Analysis toolpacks installed.
I guess a better way to describe what I am trying to do is I want to find
out how long it will take me to travel from point A to point B, knowing my
rate of travel and the distance between points A and B.

Any ideas?

Thanks,

Rich

 

[GSpline]

Well, feel kinda silly... I was reading another post and saw a reference to
using a Worksheet function, so I tried that
(Application.WorksheetFunction.RoundDown(X,Y) and my sub now seems to
function as expected. If anyone has thoughts on the helpfile issue I
described I would still be interested in hearing them.

Thanks,

Rich

 

[Ron Rosenfeld]

I am attempting to convert a variable number of seconds to a readout that
shows Hrs:Min:Sec. I have been trying to just convert the seconds to hours,
round the number of hours to the nearest (and lowest) whole number then
subtract the number of hours (in seconds) from the original number of seconds
and then do the same thing with the minutes field. The rounding does not do
what I am intending as it sometimes rounds up instead of down. I have tried
to look in my excel VBA online help but when the search results display & I
click on the item of interest it will not do anything, will not show the
requested help file. I tried to use the ROUNDDOWN function but I am told
that the Sub or Function is not defined. I checked in excel and I have the
Analysis toolpacks installed.
I guess a better way to describe what I am trying to do is I want to find
out how long it will take me to travel from point A to point B, knowing my
rate of travel and the distance between points A and B.

Any ideas?

Thanks,

Rich

1. One way of getting a readout is to divide by 86400 (number of seconds in a
day) and display as time. Excel stores time as days and fractions of days.

For example:

Sub foo()
Const NumSecs As Double = 39678
Debug.Print Application.WorksheetFunction.Text(NumSecs / 86400, "[h]:mm:ss")
Debug.Print _
Application.WorksheetFunction.Text _
(NumSecs / 86400, "[h]""Hrs"":m""Min"":ss""Sec""")
End Sub

11:01:18
11Hrs:1Min:18Sec


--ron

 

[Dave Peterson]

Since Rounddown is a worksheet function, you use excel's help. (Why would you
want all the worksheet function help text duplicated in VBA's help?)

By the way, =rounddown() isn't part of the Analysis Toolpak (well, it doesn't
say it is in Excel's help <bg>.)

 

[GSpline]

This was exactly what I was trying to figure out, Ron. I was able to do this
same thing via another method but it is a bit longer than this one. In
testing your example, I kept getting errors and was finally able to get it
working by changing

Debug.Print _
Application.WorksheetFunction.Text _
(NumSecs / 86400, "[h]""Hrs"":m""Min"":ss""Sec""")

to read as

Debug.Print _
Application.WorksheetFunction.Text _
(NumSecs / 86400, "[h]""Hrs"":m""Min"":ss")&"Sec"

Is there a reason why I could not included the "Sec" within the parenthesis
like the Hrs & Min were?

Thanks again, Ron, this really helped and it seems to be a bit more accurate
than the way I was doing it.


Rich

I am attempting to convert a variable number of seconds to a readout that
shows Hrs:Min:Sec. I have been trying to just convert the seconds to hours,
round the number of hours to the nearest (and lowest) whole number then
subtract the number of hours (in seconds) from the original number of seconds
and then do the same thing with the minutes field. The rounding does not do
what I am intending as it sometimes rounds up instead of down. I have tried
to look in my excel VBA online help but when the search results display & I
click on the item of interest it will not do anything, will not show the
requested help file. I tried to use the ROUNDDOWN function but I am told
that the Sub or Function is not defined. I checked in excel and I have the
Analysis toolpacks installed.
I guess a better way to describe what I am trying to do is I want to find
out how long it will take me to travel from point A to point B, knowing my
rate of travel and the distance between points A and B.

Any ideas?

Thanks,

Rich

1. One way of getting a readout is to divide by 86400 (number of seconds in a
day) and display as time. Excel stores time as days and fractions of days.

For example:

Sub foo()
Const NumSecs As Double = 39678
Debug.Print Application.WorksheetFunction.Text(NumSecs / 86400, "[h]:mm:ss")
Debug.Print _
Application.WorksheetFunction.Text _
(NumSecs / 86400, "[h]""Hrs"":m""Min"":ss""Sec""")
End Sub

11:01:18
11Hrs:1Min:18Sec


--ron

 

[Ron Rosenfeld]

This was exactly what I was trying to figure out, Ron. I was able to do this
same thing via another method but it is a bit longer than this one. In
testing your example, I kept getting errors and was finally able to get it
working by changing

Debug.Print _
Application.WorksheetFunction.Text _
(NumSecs / 86400, "[h]""Hrs"":m""Min"":ss""Sec""")

to read as

Debug.Print _
Application.WorksheetFunction.Text _
(NumSecs / 86400, "[h]""Hrs"":m""Min"":ss")&"Sec"

Is there a reason why I could not included the "Sec" within the parenthesis
like the Hrs & Min were?

Thanks again, Ron, this really helped and it seems to be a bit more accurate
than the way I was doing it.


Rich

I don't know why you get the error. I copied your first example above and
pasted into a module. It works correctly without errors.

Perhaps in your module it is not exactly as you have it in your post? When I
get errors in this sort of thing, it's usually because I've got the wrong
number of quote marks someplace.

In any event, I'm glad this approach is working for you.


--ron