We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Multiplying time

asked 2018-07-09 17:58:16 +0200

BrianM gravatar image

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,


edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-07-09 18:16:26 +0200

JohnSUN gravatar image

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


edit flag offensive delete link more


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.

ve3oat gravatar imageve3oat ( 2018-07-09 21:29:36 +0200 )edit

... 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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-07-10 05:26:54 +0200 )edit

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.

JohnSUN gravatar imageJohnSUN ( 2018-07-10 06:09:26 +0200 )edit

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

BrianM gravatar imageBrianM ( 2018-07-10 23:24:50 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-07-09 17:58:16 +0200

Seen: 977 times

Last updated: Jul 09 '18