Total Elapsed Time

[Bruce Rodtnick]

I have a database with a timesheet form that I'm reporting StartTime,
EndTime and ElapsedTime. ElapsedTime is calculated using a HoursAndMinutes
minutes function: =HoursAndMinutes([StartTime]-[EndTime]) for each record
and then a total time for the employee:
=HoursAndMinutes(Sum([StartTime]-[EndTime]))

All that works great, but I want to be able to total up all the time for the
project (there's only one project) from all employees. I've not been
successful. I'm trying:
=HoursAndMinutes((Sum(TimeSheet!StartTime))-(Sum(TimeSheet!EndTime))) and
getting an error.

How can I total the entire table?

Bruce

 

[John Vinson]

I have a database with a timesheet form that I'm reporting StartTime,
EndTime and ElapsedTime. ElapsedTime is calculated using a HoursAndMinutes
minutes function: =HoursAndMinutes([StartTime]-[EndTime]) for each record
and then a total time for the employee:
=HoursAndMinutes(Sum([StartTime]-[EndTime]))

All that works great, but I want to be able to total up all the time for the
project (there's only one project) from all employees. I've not been
successful. I'm trying:
=HoursAndMinutes((Sum(TimeSheet!StartTime))-(Sum(TimeSheet!EndTime))) and
getting an error.

How can I total the entire table?

Bruce

I would guess that HoursAndMinutes is returning a text string (e.g.
"7:55"). Text strings cannot be summed; and it makes no sense to sum
StartTime and EndTime - what is the sum of 8:05AM, 7:58AM, and 9:35AM
(hey, I'm self employed and lazy)?

You'll want to calculate a number of minutes in a Long Integer field
and sum THAT:

Sum(DateDiff("n", [StartTime], [EndTime]))

This can be formatted for display has hours and minutes using an
expression like

Sum(DateDiff("n", [StartTime], [EndTime])) \ 60 & ":" &
Format(Sum(DateDiff("n", [StartTime], [EndTime])) MOD 60, "00")

John W. Vinson[MVP]

 

[Bruce Rodtnick]

That worked much easier than what I was doing, but still....How would I
total up the whole table? I tried to modify your code with:
=Sum(DateDiff("n",[TimeSheet]![StartTime],[TimeSheet]![EndTime]))\60 & ":" &
Format(Sum(DateDiff("n",[TimeSheet]![StartTime],[TimeSheet]![EndTime])) Mod
60,"00").

[TimeSheet} is the table.

Bruce

I have a database with a timesheet form that I'm reporting StartTime,
EndTime and ElapsedTime. ElapsedTime is calculated using a
HoursAndMinutes
minutes function: =HoursAndMinutes([StartTime]-[EndTime]) for each record
and then a total time for the employee:
=HoursAndMinutes(Sum([StartTime]-[EndTime]))

All that works great, but I want to be able to total up all the time for
the
project (there's only one project) from all employees. I've not been
successful. I'm trying:
=HoursAndMinutes((Sum(TimeSheet!StartTime))-(Sum(TimeSheet!EndTime))) and
getting an error.

How can I total the entire table?

Bruce

I would guess that HoursAndMinutes is returning a text string (e.g.
"7:55"). Text strings cannot be summed; and it makes no sense to sum
StartTime and EndTime - what is the sum of 8:05AM, 7:58AM, and 9:35AM
(hey, I'm self employed and lazy)?

You'll want to calculate a number of minutes in a Long Integer field
and sum THAT:

Sum(DateDiff("n", [StartTime], [EndTime]))

This can be formatted for display has hours and minutes using an
expression like

Sum(DateDiff("n", [StartTime], [EndTime])) \ 60 & ":" &
Format(Sum(DateDiff("n", [StartTime], [EndTime])) MOD 60, "00")

John W. Vinson[MVP]

 

[John Vinson]

That worked much easier than what I was doing, but still....How would I
total up the whole table? I tried to modify your code with:
=Sum(DateDiff("n",[TimeSheet]![StartTime],[TimeSheet]![EndTime]))\60 & ":" &
Format(Sum(DateDiff("n",[TimeSheet]![StartTime],[TimeSheet]![EndTime])) Mod
60,"00").

[TimeSheet} is the table.

To total the whole table, use the DSum() function instead of the query
Sum operator. The syntax is slightly different - you need to pass each
argument as a string:

DSum("DateDiff(""n"",[StartTime],[EndTime])", "[TimeSheet]")

in place of the Sum() expression above.

John W. Vinson[MVP]

 

[Bruce Rodtnick]

That worked....partially. I got it to add the table but struck out on the
Format part of it. I tried:
=DSum("DateDiff(""n"",[TimeIn],[TimeOut])","[TimeSheet]")\60 & ":" &
Format(DSum("DateDiff(""n"",[TimeIn],[TimeOut]") Mod 60,"00"))

and got "The expression you entered has a function containing the wrong
number of arguments". Where am I wrong?

Thanks fro your help.

Bruce

That worked much easier than what I was doing, but still....How would I
total up the whole table? I tried to modify your code with:
=Sum(DateDiff("n",[TimeSheet]![StartTime],[TimeSheet]![EndTime]))\60 & ":"
&
Format(Sum(DateDiff("n",[TimeSheet]![StartTime],[TimeSheet]![EndTime]))
Mod
60,"00").

[TimeSheet} is the table.

To total the whole table, use the DSum() function instead of the query
Sum operator. The syntax is slightly different - you need to pass each
argument as a string:

DSum("DateDiff(""n"",[StartTime],[EndTime])", "[TimeSheet]")

in place of the Sum() expression above.

John W. Vinson[MVP]

 

[John Vinson]

That worked....partially. I got it to add the table but struck out on the
Format part of it. I tried:
=DSum("DateDiff(""n"",[TimeIn],[TimeOut])","[TimeSheet]")\60 & ":" &
Format(DSum("DateDiff(""n"",[TimeIn],[TimeOut]") Mod 60,"00"))

and got "The expression you entered has a function containing the wrong
number of arguments". Where am I wrong?

I probably miscounted parentheses... let's see: yep, missed a closing
paren after the two DSum calls, and the obligatory second argument to
DSum in the second instance:

=DSum("DateDiff(""n"",[TimeIn],[TimeOut])","[TimeSheet]"))\60
& ":"
& Format(DSum("DateDiff(""n"",[TimeIn],[TimeOut])","[TimeSheet]")) Mod
60,"00"))


John W. Vinson[MVP]

 

[Microsoft]

I got a "too many ending parentheses" error, and played with it for a while
and came up with:
=DSum("DateDiff(""n"",[TimeIn],[TimeOut])","[TimeSheet]")\60 & ":" &
Format(DSum("DateDiff(""n"",[TimeIn],[TimeOut])","[TimeSheet]") Mod
60,"00").

Thanks for your help.

Bruce



and that worked. Thanks

That worked....partially. I got it to add the table but struck out on the
Format part of it. I tried:
=DSum("DateDiff(""n"",[TimeIn],[TimeOut])","[TimeSheet]")\60 & ":" &
Format(DSum("DateDiff(""n"",[TimeIn],[TimeOut]") Mod 60,"00"))

and got "The expression you entered has a function containing the wrong
number of arguments". Where am I wrong?

I probably miscounted parentheses... let's see: yep, missed a closing
paren after the two DSum calls, and the obligatory second argument to
DSum in the second instance:

=DSum("DateDiff(""n"",[TimeIn],[TimeOut])","[TimeSheet]"))\60
& ":"
& Format(DSum("DateDiff(""n"",[TimeIn],[TimeOut])","[TimeSheet]")) Mod
60,"00"))

John W. Vinson[MVP]