Hi

I am currently dealing with an Access database that tracks
activity management data for my staff. The system outputs
the results of a query to Excel that details the first
record entered onto the system, and the last record
entered as time values with format "HH:MM:SS" - I then
subtract the lowest value (TimeL) from the highest (TimeH)
to give me an indication of the time users spent at work
(TimeDiff).

However, when I try to add the TimeDiff values together to
give a total number of hours, I get an odd value - all I'm
looking to do is total the number of hours together. Is
there an easy way to do this?

TIA

Hi Kevin

You need to multiply the values by 24, as Excel stores its Time values as
fractions of a day.
If you are summing data, format the cell holding the summation formula with
Custom format [hh]:mm to allow it to sum past 24.

--
Regards
Roger Govier
"Kevin Gordon" wrote in message
...
Hi

I am currently dealing with an Access database that tracks
activity management data for my staff. The system outputs
the results of a query to Excel that details the first
record entered onto the system, and the last record
entered as time values with format "HH:MM:SS" - I then
subtract the lowest value (TimeL) from the highest (TimeH)
to give me an indication of the time users spent at work
(TimeDiff).

However, when I try to add the TimeDiff values together to
give a total number of hours, I get an odd value - all I'm
looking to do is total the number of hours together. Is
there an easy way to do this?

TIA

Just to add -
Roger has given you two separate approaches.

to get integers representing hours, multiply by 24

to continue to work with time values, in your sum, format as he proposed -
this will tell Excel to display the results as the total number of hours
(rather than the default days and hours where days might not show up
depending on your format).

--
Regards,
Tom Ogilvy


"Roger Govier" wrote in message
...
Hi Kevin

You need to multiply the values by 24, as Excel stores its Time values as
fractions of a day.
If you are summing data, format the cell holding the summation formula
with
Custom format [hh]:mm to allow it to sum past 24.

--
Regards
Roger Govier
"Kevin Gordon" wrote in message
...
Hi

I am currently dealing with an Access database that tracks
activity management data for my staff. The system outputs
the results of a query to Excel that details the first
record entered onto the system, and the last record
entered as time values with format "HH:MM:SS" - I then
subtract the lowest value (TimeL) from the highest (TimeH)
to give me an indication of the time users spent at work
(TimeDiff).

However, when I try to add the TimeDiff values together to
give a total number of hours, I get an odd value - all I'm
looking to do is total the number of hours together. Is
there an easy way to do this?

TIA

Thanks Tom

I had not made that at all clear that the 2 statements were differing
approaches to the problem.

--
Regards
Roger Govier
"Tom Ogilvy" wrote in message
...
Just to add -
Roger has given you two separate approaches.

to get integers representing hours, multiply by 24

to continue to work with time values, in your sum, format as he proposed -
this will tell Excel to display the results as the total number of hours
(rather than the default days and hours where days might not show up
depending on your format).

--
Regards,
Tom Ogilvy


"Roger Govier" wrote in message
...
Hi Kevin

You need to multiply the values by 24, as Excel stores its Time values
as
fractions of a day.
If you are summing data, format the cell holding the summation formula
with
Custom format [hh]:mm to allow it to sum past 24.

--
Regards
Roger Govier
"Kevin Gordon" wrote in message
...
Hi

I am currently dealing with an Access database that tracks
activity management data for my staff. The system outputs
the results of a query to Excel that details the first
record entered onto the system, and the last record
entered as time values with format "HH:MM:SS" - I then
subtract the lowest value (TimeL) from the highest (TimeH)
to give me an indication of the time users spent at work
(TimeDiff).

However, when I try to add the TimeDiff values together to
give a total number of hours, I get an odd value - all I'm
looking to do is total the number of hours together. Is
there an easy way to do this?

TIA

Hi,
I’ve got the same problem and I don’t get it.
I have a time sheet with 7 days , Clock In and Clock Out
Subtract the two and get the hours for the day. That part works.
But when I add them I get a strange number.
I have the cell formatted like you said [hh]:mm

Here are the Hours for the day
mon 10:00
tue 9:17
wed 12:00

I know the total is 31.17
Excel says its 24.00

WHY DO IT BE THIS WAY! AHHHH!


wssparky
I’m a product of public schools so go slow.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi
are all hours for a day formated as time?

--
Regards
Frank Kabel
Frankfurt, Germany

wssparky wrote:
Hi,
I've got the same problem and I don't get it.
I have a time sheet with 7 days , Clock In and Clock Out
Subtract the two and get the hours for the day. That part works.
But when I add them I get a strange number.
I have the cell formatted like you said [hh]:mm

Here are the Hours for the day
mon 10:00
tue 9:17
wed 12:00

I know the total is 31.17
Excel says its 24.00

WHY DO IT BE THIS WAY! AHHHH!


wssparky
I'm a product of public schools so go slow.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Kevin
format the target cell with the custom format [h]:mm. This will prevent
the roll over after 24 hours.

Frank

Kevin Gordon wrote:
Hi

I am currently dealing with an Access database that tracks
activity management data for my staff. The system outputs
the results of a query to Excel that details the first
record entered onto the system, and the last record
entered as time values with format "HH:MM:SS" - I then
subtract the lowest value (TimeL) from the highest (TimeH)
to give me an indication of the time users spent at work
(TimeDiff).

However, when I try to add the TimeDiff values together to
give a total number of hours, I get an odd value - all I'm
looking to do is total the number of hours together. Is
there an easy way to do this?

TIA