Time Calculation

[JinkyJulie via OfficeKB.com]

Hi again...

I'm lost... I know that Word does not handle time calculation well... but I
do have some code someone graciously assisted me with... and it works BUT...
there are some minor issues that I cannot seem to fix....

Here goes....

Two tables.... Take time info from one table, calculate elapsed time and
send result to second table. Code to do that works great...

All times are in 24 hour, HH:mm format (03:47, 14:53, etc...)

Code has a problem if time is midnight (00:00) and get a Type Mismatch error
if the time is changed to 24:00.

Result is wrong if times are "simple"... (e.g. 06:26 and 04:31 results in 3:
51 elapsed time) I don't understand why...

nHr = DateDiff("h", CDate(StartTime), CDate(EndTime))
nMin = DateDiff("n", CDate(StartTime), CDate(EndTime))
ResultRange.Text = Format(nHr, "#") & ":" & Format(nMin Mod 60, "##")

Can someone please give me a hand??? I would really appreciate it...

Thanks as always....

Julie

 

[Karl E. Peterson]

Hi again...

I'm lost... I know that Word does not handle time calculation well... but I
do have some code someone graciously assisted me with... and it works BUT...
there are some minor issues that I cannot seem to fix....

Here goes....

Two tables.... Take time info from one table, calculate elapsed time and
send result to second table. Code to do that works great...

All times are in 24 hour, HH:mm format (03:47, 14:53, etc...)

Code has a problem if time is midnight (00:00) and get a Type Mismatch error
if the time is changed to 24:00.

Result is wrong if times are "simple"... (e.g. 06:26 and 04:31 results in 3:
51 elapsed time) I don't understand why...

nHr = DateDiff("h", CDate(StartTime), CDate(EndTime))
nMin = DateDiff("n", CDate(StartTime), CDate(EndTime))
ResultRange.Text = Format(nHr, "#") & ":" & Format(nMin Mod 60, "##")

Can someone please give me a hand??? I would really appreciate it...

I think you're working too hard. I have trouble even deciphering the intent there,
so that's the first problem. If the code isn't readable, and intuitively obvious,
something's probably wrong. It looks to me like you're trying to find the hours and
minutes between two timestamps? How about this:

RangeResult.Text = Format$((EndTime - StartTime), "hh:mm")

?

 

[JinkyJulie via OfficeKB.com]

Hi Karl,

Thanks for the quick reply... your suggestion looks like it would work but...
Type Mismatch errors

I am trying to take two time values (that are stored in a table as TEXT...),
calculate the difference between the two and display the result in another
table...

The code I provided works.... BUT for those issues I mentioned...

As mentioned, I know that Word isn't built for this.... but MS Word is what I
am stuck with...

Thanks again....

Julie

 

[Karl E. Peterson]

Thanks for the quick reply... your suggestion looks like it would work but...
Type Mismatch errors

Well, then that's what you need to address, rather than criticize what's not
misunderstood. (Said with a smile, of course.)

I am trying to take two time values (that are stored in a table as TEXT...),

So you need to convert the text to date/time then. You seemed to know how to do
that, as you used CDate in your original post, so I'm sorry if I assumed more than I
should've. If you want a complete solution, it's always best to provide a complete
problem.

calculate the difference between the two and display the result in another
table...

The code I provided works.... BUT for those issues I mentioned...

The code that I provided works, too, as long as the proper data formats are fed into
it. To be a programmer, the very *first* thing one must come to grips with is data
types -- what each is, what each is best used for, how to convert/coerce between
each.

As mentioned, I know that Word isn't built for this.... but MS Word is what I
am stuck with...

Word knows diddly about this. What you're using is VBA. Word happens to host VBA.
VBA is *incredibly* powerful, and absolutely "built for this."

So, back to the coerction/conversion issue, what type are your StartTime and EndTime
variables?

 

[JinkyJulie via OfficeKB.com]

Karl,

I am learning as I go... and many here have been very helpful....

I will provide the entire code as provided by others and "tweaked" to suit
what I am doing... (note... I am new to VBA and most of this was others'
expertise. For that I am appreciative...)

Dim FirstTable As Table
Dim SecondTable As Table

Dim StartTimeCell As Cell
Dim EndTimeCell As Cell
Dim ResultCell As Cell

Dim StartTimeRange As Range
Dim EndTimeRange As Range
Dim ResultRange As Range

Dim StartTime As Long
Dim EndTime As Long

Dim nHr As Long
Dim nMin As Long

Set FirstTable = ActiveDocument.Tables(1)
Set SecondTable = ActiveDocument.Tables(2)

Set StartTimeCell = FirstTable.Cell( _
Row:=3, Column:=1)

Set EndTimeCell = FirstTable.Cell( _
Row:=FirstTable.Rows.Count, Column:=1)

Set ResultCell = SecondTable.Cell( _
Row:=3, Column:=2)

Set StartTimeRange = StartTimeCell.Range
StartTimeRange.MoveEnd wdCharacter, -1

Set EndTimeRange = EndTimeCell.Range
EndTimeRange.MoveEnd wdCharacter, -1

Set ResultRange = ResultCell.Range
ResultRange.MoveEnd wdCharacter, -1

StartTime = StartTimeRange.Text
EndTime = EndTimeRange.Text

nHr = DateDiff("h", CDate(StartTime), CDate(EndTime))
nMin = DateDiff("n", CDate(StartTime), CDate(EndTime))
ResultRange.Text = Format(nHr, "#") & ":" & Format(nMin Mod 60, "##")

Does this help???

Julie

 

[Karl E. Peterson]

Hi Jinks --

I am learning as I go... and many here have been very helpful....

We'll try to go slow, then.

You'll have to forgive folks like me - I was there nearly three decades ago.

I will provide the entire code as provided by others and "tweaked" to suit
what I am doing... (note... I am new to VBA and most of this was others'
expertise. For that I am appreciative...)

Okay, I'll leave it intact below, for the thread's ongoing sanity. Here's the first
problem:

Dim StartTime As Long
Dim EndTime As Long

VB/A stores datetime values in a Double precision variable, with the date in the
integer part and the time in the fractional part. The Date datatype is really just
a Double in drag. Now is a Date. To see how this is stored, under the covers, try
this in the Immediate window:

?cdbl(now)
39905.5309375

So today is the 39905th day after day 0. When was day 0? Again, in the Immediate
window:

?format(0, "short date")
12/30/1899

More fun with dates, again, in the Immediate window:

?format(39905, "short date")
4/2/2009
?format(39906, "short date")
4/3/2009
?format(39910, "short date")
4/7/2009

The .5309375 indicates it's just a little past noon (0.5) here. Again, in the
Immediate window:

?format(0.5309375, "short time")
12:44

As an aside, are you seeing a pattern here? <g> If you wonder how something works,
try it! That's what the Immediate window is for. This *must* become your first
instinct. Anyway, back to the main theme...

What's all this telling us? Well, you've apparently used Long values to store
times. By definition, Long variables have *no* fractional part -- they're strictly
integers. IOW, any "time" stored in a Long will, again by definition, be midnight.
So there's the basis of the first problem.

Getting back to what you percieved as the problem:

StartTime = StartTimeRange.Text
EndTime = EndTimeRange.Text

nHr = DateDiff("h", CDate(StartTime), CDate(EndTime))
nMin = DateDiff("n", CDate(StartTime), CDate(EndTime))
ResultRange.Text = Format(nHr, "#") & ":" & Format(nMin Mod 60, "##")

May I ask what the text looks like in StartTimeRange and EndTimeRange? Oh, that's
right, you said in the first post:

All times are in 24 hour, HH:mm format (03:47, 14:53, etc...)

Okay, so what you need to do is go back and change the declares for those variables
as such:

Dim StartDate As Date
Dim EndDate As Date

Then, change the assignments, so they convert from text to date at that point:

StartTime = CDate(StartTimeRange.Text)
EndTime = CDate(EndTimeRange.Text)

Now, all that DateDiff stuff becomes wasted effort. You're back to the formula I
offered originally:

RangeResult.Text = Format$((EndTime - StartTime), "hh:mm")

Big lesson: Know your variable types! There are only a handful. Far fewer than
there are letters in the alphabet, but just as a kindergardner can't learn to read
without knowing those, a programmer cannot function without understanding these.

Questions?

Regards... Karl

 

[Jonathan West]

Hi Karl

Just one minor quibble

Dim StartDate As Date
Dim EndDate As Date

should be

Dim StartTime As Date
Dim EndTime As Date

Otherwise the names declared and the names used don't match.

 

[JinkyJulie via OfficeKB.com]

Hello again all...

It works great!!! And a lot less "bulky". Thanks Karl and Jonathan...

With some research I found out that the 00:00 vs 24:00 thing... well....
whatever... I created a small workaround... not pretty but it works....

Thanks again.... your help is appreciated.....

Julie....

 

[Karl E. Peterson]

Hi Karl

Just one minor quibble


should be

Whoops! Yep, typed that one too quick, didn't I? Thanks...

(Is this where we *encourage* the newb to use Option Explicit? <g>)

 

[Karl E. Peterson]

Hello again all...

It works great!!! And a lot less "bulky". Thanks Karl and Jonathan...

Good to hear.

With some research I found out that the 00:00 vs 24:00 thing... well....
whatever... I created a small workaround... not pretty but it works....

What's the problem there, again? I just took a quick look, and I see that this:

?cdate("24:00")

throws a "Type Mismatch" error. Is that it? What was the workaround?

(We're good, at times, with lipstick and pigs around here. <g>)