TIME values

[Kevin Gordon]

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

 

[Roger Govier]

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.

 

[Frank Kabel]

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

Frank

 

[Tom Ogilvy]

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

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

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

 

[Roger Govier]

Thanks Tom

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

--
Regards
Roger Govier

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

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

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

 

[wssparky]

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.

 

[Frank Kabel]

Hi
are all hours for a day formated as time?