Totaling Time In a Report

[Guest]

Hello:

I have a simple form that calculates elapsed time for a work day. The
expression that calculates the hours worked for the Total_Time field is:

=HoursAndMinutes([End_Time]-[Start_Time])

and that works perfectly.

However, I want to calculate the TOTAL hours worked per Pay Period. I placed
the following expression in the Pay_Period Group Footer. Instead of totaling
up the number of hours worked each pay period, I receive a data mismatch
error. What am I doing wrong?

=Sum(HoursAndMinutes([End_Time]-[Start_Time]))

Thanks,
Robert

 

[Duane Hookom]

We can't tell without seeing the function HoursAndMinutes. I expect it
returns a string which would cause an error. Try:
=HoursAndMinutes(Sum([End_Time]-[Start_Time]))

 

[Guest]

Try using:
=HoursAndMinutes(Sum([End_Time]-[Start_Time]))

Hello Duane:

The above expression worked like a charm but it brings up two questions.

[1] Why did your expression work while mine [with sum at the beginnng of the
expression] failed?

[2] It totaled up the time in hours and minutes which is what I wanted, so
that's a good thing. However, what expression could I use if I wanted the
totals in a numeric value as in:

75.50 hours as opposed to 76:30 [76 hours and 30 minutes]

Thanks for your help,
Robert T

 

[Duane Hookom]

Date/Time values are numeric values with the value left of the decimal as
the number of days since Dec 30, 1899. The value to the right of the decimal
is the fraction of a day. A decimal of .25 is one quarter day. When
DISPLAYED using a time format, one quarter day is 6:00 AM.

Now() is approximately 38474.4125231481.

This knowledge should answer both your questions.


1) I assume HoursAndMinutes() returns a sting value. You can't sum a string.
I assumed your function accepted a numeric value as the argument and
Sum([End_Time]-[Start_Time]) satisfies that requirement.

2) Sum([End_Time]-[Start_Time]) * 24
Sum([End_Time]-[Start_Time]) will return the number of days. Multiply
days times the number of hours in a day to get total hours.

 

[Guest]

Hello Duane:

Thanks for the explanation, that information should give me enough to build
the expressions I need.

Your help was truly appreciated,
Robert T