Summing Time in an Expression

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I have several fields that are formatted as "short time"
(e.g. 10:00). I need to create a new field using an
expression that would total these hours. When I try an
expression like "Total Hours: ([Field 1]+[Field 2]+[Field
3])" it will sometimes add correctly, other times not. Is
there a specific funtion for adding hours?
 
How a field is formatted has no effect on how the data are stored for
date/time fields. All dates with or without times, and all times with or
without dates, are stored as decimal numbers, where the date is the integer
portino (the number of full days since midnight, December 30, 1899), and the
time is the fractional part (the fraction of a 24-hour day represented by
the time).

However, it appears from your description that what you want to be storing
is the actual elapsed minutes, not the actual time. If you store the minutes
(elapsed minutes) in Field 1, and in Field 2, and in Field 3, you then can
add the fields together and convert from total minutes to total hours (and
fractions of hours).

If you want to store a start time and a stop time in fields, you then can
get the difference by using the DateDiff function. I would recommend using
it to get the elapsed minutes or seconds, and then to sum the differences
into a total, and then to convert to hours and/or fractional hours.
 
Dave said:
I have several fields that are formatted as "short time"
(e.g. 10:00). I need to create a new field using an
expression that would total these hours. When I try an
expression like "Total Hours: ([Field 1]+[Field 2]+[Field
3])" it will sometimes add correctly, other times not. Is
there a specific funtion for adding hours?
Click to expand...

Hi Dave,

I agree with Ken that elapsed time
should be stored as units of time
(like number of seconds), the Date/Time
type is most efficient only at storing
points in time.

Below are results from the Immediate window
that may help understand what is going on:

Imagine you are looking at a record with
the following values for 3 Date/Time fields:

time1=#10:00#
time2=#20:00#
time3=null

and you expect to add them to get "30:00"

-- what is "really stored"

?format(time1,"mm/dd/yyyy hh:nn")
12/30/1899 10:00

?format(time2,"mm/dd/yyyy hh:nn")
12/30/1899 20:00

-- what you get from adding them

?time1 + time2
12/31/1899 6:00:00 AM

?format(time1 + time2,"hh:nn")
06:00

the "sum" was greater than 24 hours,
so the *day* was incremented by 1
and the time of the sum only gets the
remainder.

-- "+" propagates Nulls

?time1 + time2 + time3
Null

-- workaround for any Nulls

?Nz(time1,#00:00#) + Nz(time2,#00:00#) + Nz(time3,#00:00#)
12/31/1899 6:00:00 AM

-- one workaround to get "hours:minutes" for sum
(this should be all in one line...
just replace "timex" with name of your fields)

?Format(DateDiff("n",#00:00#,(Nz(time1,#00:00#)
+ Nz(time2,#00:00#) + Nz(time3,#00:00#)))\60,"00")
& ":" & Format(DateDiff("n",#00:00#,(Nz(time1,#00:00#)
+ Nz(time2,#00:00#) + Nz(time3,#00:00#))) Mod 60,"00")

giving you:

30:00

That's quite a "mouthful"....
it would be a lot more efficient if the
elapsed times had just been stored
as number of seconds (type Long)
as Ken aptly suggested.

If that is not an option, one other workaround
might be to run a "prequery" that computes
the elapsed times, something like:

ElapSecField1: DateDiff("n",#00:00#,Nz([Field1],#00:00#))
ElapSecField2: DateDiff("n",#00:00#,Nz([Field2],#00:00#))
ElapSecField3: DateDiff("n",#00:00#,Nz([Field3],#00:00#))

Then use this "prequery" in 2nd query that computes "formatted sum"

Format((ElapSecField1+ElapSecField2+ElapSecField3)\60,"00")
& ":" & Format((ElapSecField1+ElapSecField2+ElapSecField3) MOD 60,"00")

Note that the "hour Format portion" above
assumes you will never have hour sum > "99".
If that is not the case, then use "000" instead of "00".

Maybe that helps.

Good luck,

Gary Walter
 
Actually... if I had to store elapsed times in Date/Time
fields like you have, my query would only compute
a "number of seconds" sum, then in a form or report
I would then format to "xx:xx" if needed.

ElapSecSum: DateDiff("n",#00:00#,(Nz(time1,#00:00#) + Nz(time2,#00:00#) +
Nz(time3,#00:00#)))

the reason being that "xx:xx" is just a string...
it is not a Date/Time and it is not a number..
so you cannot do any further processing with it...


Gary Walter said:
Dave said:
I have several fields that are formatted as "short time"
(e.g. 10:00). I need to create a new field using an
expression that would total these hours. When I try an
expression like "Total Hours: ([Field 1]+[Field 2]+[Field
3])" it will sometimes add correctly, other times not. Is
there a specific funtion for adding hours?
Click to expand...

Hi Dave,

I agree with Ken that elapsed time
should be stored as units of time
(like number of seconds), the Date/Time
type is most efficient only at storing
points in time.

Below are results from the Immediate window
that may help understand what is going on:

Imagine you are looking at a record with
the following values for 3 Date/Time fields:

time1=#10:00#
time2=#20:00#
time3=null

and you expect to add them to get "30:00"

-- what is "really stored"

?format(time1,"mm/dd/yyyy hh:nn")
12/30/1899 10:00

?format(time2,"mm/dd/yyyy hh:nn")
12/30/1899 20:00

-- what you get from adding them

?time1 + time2
12/31/1899 6:00:00 AM

?format(time1 + time2,"hh:nn")
06:00

the "sum" was greater than 24 hours,
so the *day* was incremented by 1
and the time of the sum only gets the
remainder.

-- "+" propagates Nulls

?time1 + time2 + time3
Null

-- workaround for any Nulls

?Nz(time1,#00:00#) + Nz(time2,#00:00#) + Nz(time3,#00:00#)
12/31/1899 6:00:00 AM

-- one workaround to get "hours:minutes" for sum
(this should be all in one line...
just replace "timex" with name of your fields)

?Format(DateDiff("n",#00:00#,(Nz(time1,#00:00#)
+ Nz(time2,#00:00#) + Nz(time3,#00:00#)))\60,"00")
& ":" & Format(DateDiff("n",#00:00#,(Nz(time1,#00:00#)
+ Nz(time2,#00:00#) + Nz(time3,#00:00#))) Mod 60,"00")

giving you:

30:00

That's quite a "mouthful"....
it would be a lot more efficient if the
elapsed times had just been stored
as number of seconds (type Long)
as Ken aptly suggested.

If that is not an option, one other workaround
might be to run a "prequery" that computes
the elapsed times, something like:

ElapSecField1: DateDiff("n",#00:00#,Nz([Field1],#00:00#))
ElapSecField2: DateDiff("n",#00:00#,Nz([Field2],#00:00#))
ElapSecField3: DateDiff("n",#00:00#,Nz([Field3],#00:00#))

Then use this "prequery" in 2nd query that computes "formatted sum"

Format((ElapSecField1+ElapSecField2+ElapSecField3)\60,"00")
& ":" & Format((ElapSecField1+ElapSecField2+ElapSecField3) MOD 60,"00")

Note that the "hour Format portion" above
assumes you will never have hour sum > "99".
If that is not the case, then use "000" instead of "00".

Maybe that helps.

Good luck,

Gary Walter
Click to expand...
 
Back
Top