Collect, add times in a looped array?

[Ed]

I want to pick up times from a document in the form of hh:mm and write them
into an array. As the array loops, the next time will be added to the
previous array value. In simulated code:
For x = 1 To 6
Time = MyRange.Text
aryTime(x) = Time + aryTime(x)
Next x

How do I format Time so it becomes a value I can add? What is the correct
way to add times within code?

Ed

 

[Helmut Weber]

Hi Ed,

first,
see help for "time",
time function and time statement

second, I miss the concept of reserved words in VBA,
this shouldn't be possible, IMHO.

Dim Time As Double
MsgBox Time

as opposed to, just:
MsgBox Time

Third,
this could become a long thread,
as in principle, adding times as 6 o'clock plus 9 o'clock,
time points, is meaningless.
A point in time is calculated from another point in time.
An absolute fix point is not available.
Time ("What time is it")
and time span ("How much time did you need?")
are something essentially different.

You may calculate with days, hours, minutes, seconds.
Beware, months and years are of varying lenght.

You may check, according to the precision you need,
how much seconds are between two points in time.

For time points in a day, e.g., leaving theory aside,
see: datediff function.

What is it You want to achieve?

To the best of my knowledge.
If someone knows better,
she or he will speak up.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Ed]

HI, Helmut. I read through all the Helps on "Time", but didn't find
anything that addresses what I was thinking of. It's quite possible I
simply don't understand how to mesh what I read with what I want to do.

I scan plain text documents for data points and write the data into an Excel
spreadsheet. In one section of the document, there are lists of times.
These are not clock times as "3:45 pm", but the length of time a job took to
complete stated as "03:45", meaning 3 hours and 45 minutes.

I'm using an array because each line will have four different time spans.
My idea was to scan through a line, collect each time and write it to the
proper place in the array, then drop to the next line and add the time span
to the previous value. When I've reached the last line, the code will drop
out of the loop and the array values will be written to the spreadsheet.
All of this I can do, except for getting the times to simply be the
quantities of hours and minutes and add together as such. Everything I've
tried wants these to be clock times.

Any nudge you can give to put my thinking in the right direction will be
greatly appreciated.

Ed

 

[Helmut Weber]

Hi Ed,

Sub Macro3()
Dim t1 As Long ' seconds from zero at point 1
Dim t2 As Long ' seconds from zero at point 2

t1 = Hour("01:37") * 3600 + Second("01:37")
t2 = Hour("13:39") * 3600 + Second("13:39")

MsgBox t2 - t1

' there are 43200 seconds between t1 and t2

End Sub

To best of my knowledge.



--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Jay Freedman]

Hi Ed,

I wouldn't even attempt to do this job in Word and VBA. Excel already has
all the tools you need built in. Have a look at the Excel help topic "Add
times" -- all you need to do is enter the durations in consecutive cells,
and write a simple formula to add them and display the total in another
cell.

If you need this to be in a Word report, either embed the Excel worksheet in
the Word document or use IncludeText fields to pull in the worksheet
contents.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.