Multiplying time

[BrianM]

I need to keep hours for a group of volunteers and report the total monthly. Sometimes they work in groups and sometimes they work individually. I created a spreadsheet with columns for start time, end time, elapsed time, and number of volunteers present. I formatted the first two time columns HH:MM AM/PM and the elapsed time column HH:MM. To get the elapsed time, I subtract start time from end time and that does display the correct elapsed time.
Then it goes haywire. At first I tried to sum() the elapsed time column and then multiply it by the sum() of the number of volunteers present column. Somehow 13:00 x 12 computes to 16:00. I tried just manually multiplying the total sum() of the elapsed time column by 12 and got the same result.
Can anyone tell me what I’m doing wrong, or how to achieve my objective? I don’t want to have to make an entire row entry for each volunteer because the report would become very large, very fast and also because there are a lot more columns of other types of data that I don’t want to have to replicate for each volunteer.
Thanks,

–Brian

[JohnSUN]

Just try to apply another time format to the cell with the result - format code [HH]:MM:SS

[ve3oat]

In my experience, @JohnSUN’s method above should work. However, if for some reason it does not work for you, you could try converting the elapsed times to whole minutes
=HOUR(A1)*60+MINUTE(A1)

and working with them as ordinary numbers everywhere in your spreadsheet, converting back to hours:minutes at the end only if you need to for display purposes.

Doing arithmetic on times (and dates!) is often tricky in any application.

[mikekaganski]

… and another note. In LibreOffice (and other spreadsheet applications), dates and times are internally numbers, that tells how many days have passed since some moment “zero”, times being fractions of a day. So, if you use dates and times properly, then doing arithmetics with them is straightforward and easy, has less overhead than using conversions back and forth, and also has less potential for error because of wrong manual conversion - if you remember to not forget about date part.

[JohnSUN]

Yes, it’s important - don’t forget about the date part. The fact when we see 01:00 PM in the cell can actually be 12/30/99 01:00 PM, and 12/31/99 01:00 PM, or 07/10/18 01: 00 PM Simple arithmetic operations with such data can return terrible errors.

[BrianM]

Yup, that was the answer. Thank you very much, JohnSUN!