DateTime calculation dilemma

[PattiP]

I have a database of substation outages that calculates the length of the
outage (i..e. TimeOff, TimeOn). Its been working fine for a few years, but
we just encountered a situation where a total outage time did not add
properly. The total is calculated in hhr:mm format as follows:

=Int(Sum([MinutesOff]/60)) & “.†& Format(Sum([MinutesOff]) Mod 60, “00â€)

It seems this works fine except in the very rare occasion when the total
minutes equals exactly 60, which evidentally has never happened before. Then
it doesn’t add that extra minute to the total. I’m thinking it must be in
the “Mod 60†part of the statement???

Here’s the example in hh:mm:

1:01
0:02
1:00
0:02
0:32
0:03
0:03
0:08
0:03
0:03
0:03

the total shows up as 2 instead of 3.

Any help would be appreciated. I’m guessing there might be an IF statement
involved????

 

[Rob Parker]

Hi Patti,

I tried this, using a table with your MinutesOff values entered as integers.
Initially, I got a #Name error when I cut/pasted your formula, so I played
with it a little, and found the following results:

=Int(Sum([MinutesOff]/60)) & ":" & Format(Sum([MinutesOff]) Mod 60,
"00") (Note: I replaced the "." with ":") gave, as you said, an answer of
2:00

=Int(Sum([MinutesOff])/60) & ":" & Format(Sum([MinutesOff]) Mod 60,
"00") gave an answer of 3:00

The problem is that, in the first example, you are summing each MinutesOff
value divided by 60, then taking the Int portion of that value. For your
data, the values you are adding (as displayed to the default precision of a
general number in Access) are:

1.01666666666667
0.0333333333333333
1
0.0333333333333333
0.5333333333333333
0.05
0.05
0.1333333333333333
0.05
0.05

These appear to sum to 3 (via a Sum([MinutesOff]/60); but then
Int(Sum([MinutesOff]/60)) only give 2. I suspect that it's due to the
rounding errors with the irrational numbers, and perhaps the Int(...)
expression is evaluated internally in a different fashion, and the sum is
evaluating to 2.99999999999999999999....

I haven't seen this oddity before, but you can prevent it using the
bracketing in the second formula I showed at the top of this post.

HTH,

Rob

 

[PattiP]

Thanks, Rob, that worked perfectly!!
Its odd, because I had several reports for this database and all the other
reports had the correct syntax except that particular one!
(Also, the "." instead of ":" was just a typo in my message.)

Sorry it took so long for me to reply, but for some reason, I'm not able to
sign into this site at work anymore.. it seems to be blocked. Our IS dept is
working on it, so I've had to wait unitl evening to submit my messages from
home. I can read all the discussion threads, but can't sign in.... hmmmmmm!
Hope they figure it out soon... this site has been such a help in the past!!

Patti
Patti

Hi Patti,

I tried this, using a table with your MinutesOff values entered as integers.
Initially, I got a #Name error when I cut/pasted your formula, so I played
with it a little, and found the following results:

=Int(Sum([MinutesOff]/60)) & ":" & Format(Sum([MinutesOff]) Mod 60,
"00") (Note: I replaced the "." with ":") gave, as you said, an answer of
2:00

=Int(Sum([MinutesOff])/60) & ":" & Format(Sum([MinutesOff]) Mod 60,
"00") gave an answer of 3:00

The problem is that, in the first example, you are summing each MinutesOff
value divided by 60, then taking the Int portion of that value. For your
data, the values you are adding (as displayed to the default precision of a
general number in Access) are:

1.01666666666667
0.0333333333333333
1
0.0333333333333333
0.5333333333333333
0.05
0.05
0.1333333333333333
0.05
0.05

These appear to sum to 3 (via a Sum([MinutesOff]/60); but then
Int(Sum([MinutesOff]/60)) only give 2. I suspect that it's due to the
rounding errors with the irrational numbers, and perhaps the Int(...)
expression is evaluated internally in a different fashion, and the sum is
evaluating to 2.99999999999999999999....

I haven't seen this oddity before, but you can prevent it using the
bracketing in the second formula I showed at the top of this post.

HTH,

Rob

I have a database of substation outages that calculates the length of the
outage (i..e. TimeOff, TimeOn). Its been working fine for a few years,
but
we just encountered a situation where a total outage time did not add
properly. The total is calculated in hhr:mm format as follows:

=Int(Sum([MinutesOff]/60)) & "." & Format(Sum([MinutesOff]) Mod 60, "00")

It seems this works fine except in the very rare occasion when the total
minutes equals exactly 60, which evidentally has never happened before.
Then
it doesn't add that extra minute to the total. I'm thinking it must be in
the "Mod 60" part of the statement???

Here's the example in hh:mm:

1:01
0:02
1:00
0:02
0:32
0:03
0:03
0:08
0:03
0:03
0:03

the total shows up as 2 instead of 3.

Any help would be appreciated. I'm guessing there might be an IF
statement
involved????