Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenTue, 04 Jun 2019 13:54:26 +0200Multiplying timehttps://ask.libreoffice.org/en/question/160175/multiplying-time/ 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,
--BrianMon, 09 Jul 2018 17:58:16 +0200https://ask.libreoffice.org/en/question/160175/multiplying-time/Answer by JohnSUN for <p>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,</p>
<p>--Brian</p>
https://ask.libreoffice.org/en/question/160175/multiplying-time/?answer=160180#post-id-160180Just try to apply another time format to the cell with the result - format code **[HH]:MM:SS**
![FormatOfBigTime.png](/upfiles/15311529576191287.png)Mon, 09 Jul 2018 18:16:26 +0200https://ask.libreoffice.org/en/question/160175/multiplying-time/?answer=160180#post-id-160180Comment by Mike Kaganski for <p>Just try to apply another time format to the cell with the result - format code <strong>[HH]:MM:SS</strong></p>
<p><img alt="FormatOfBigTime.png" src="/upfiles/15311529576191287.png"></p>
https://ask.libreoffice.org/en/question/160175/multiplying-time/?comment=160222#post-id-160222... 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.Tue, 10 Jul 2018 05:26:54 +0200https://ask.libreoffice.org/en/question/160175/multiplying-time/?comment=160222#post-id-160222Comment by ve3oat for <p>Just try to apply another time format to the cell with the result - format code <strong>[HH]:MM:SS</strong></p>
<p><img alt="FormatOfBigTime.png" src="/upfiles/15311529576191287.png"></p>
https://ask.libreoffice.org/en/question/160175/multiplying-time/?comment=160192#post-id-160192In 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.Mon, 09 Jul 2018 21:29:36 +0200https://ask.libreoffice.org/en/question/160175/multiplying-time/?comment=160192#post-id-160192Comment by JohnSUN for <p>Just try to apply another time format to the cell with the result - format code <strong>[HH]:MM:SS</strong></p>
<p><img alt="FormatOfBigTime.png" src="/upfiles/15311529576191287.png"></p>
https://ask.libreoffice.org/en/question/160175/multiplying-time/?comment=160225#post-id-160225Yes, 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.Tue, 10 Jul 2018 06:09:26 +0200https://ask.libreoffice.org/en/question/160175/multiplying-time/?comment=160225#post-id-160225Comment by BrianM for <p>Just try to apply another time format to the cell with the result - format code <strong>[HH]:MM:SS</strong></p>
<p><img alt="FormatOfBigTime.png" src="/upfiles/15311529576191287.png"></p>
https://ask.libreoffice.org/en/question/160175/multiplying-time/?comment=160317#post-id-160317Yup, that was the answer. Thank you very much, JohnSUN!Tue, 10 Jul 2018 23:24:50 +0200https://ask.libreoffice.org/en/question/160175/multiplying-time/?comment=160317#post-id-160317Answer by Hollis B for <p>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,</p>
<p>--Brian</p>
https://ask.libreoffice.org/en/question/160175/multiplying-time/?answer=196227#post-id-196227There is the number of software’s you can use for your documentation and for arithmetic operations. I must [buy college essays online](https://www.college-paper.org/buy-college-essays-online) and the helpful article you discuss is truly useful. Thumb up and please do share other educational updates online.
Tue, 04 Jun 2019 13:51:25 +0200https://ask.libreoffice.org/en/question/160175/multiplying-time/?answer=196227#post-id-196227Answer by Despina W. Bryan for <p>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,</p>
<p>--Brian</p>
https://ask.libreoffice.org/en/question/160175/multiplying-time/?answer=196229#post-id-196229There are the number of software’s you can use for your documentation and for arithmetic operations. I must [buy college essays online](https://www.college-paper.org/buy-college-essays-online) and the helpful article you discuss is truly useful. Thumb up and please do share other educational updates online.
Tue, 04 Jun 2019 13:54:26 +0200https://ask.libreoffice.org/en/question/160175/multiplying-time/?answer=196229#post-id-196229Answer by Clyde N. Wright for <p>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,</p>
<p>--Brian</p>
https://ask.libreoffice.org/en/question/160175/multiplying-time/?answer=194971#post-id-194971There are the number of software’s you can use for your documentation and for arithmetic operations. I must [buy college essays online](https://www.college-paper.org/buy-college-essays-online) and the helpful article you discuss is truly useful. Thumb up and please do share other educational updates online.Fri, 24 May 2019 11:22:56 +0200https://ask.libreoffice.org/en/question/160175/multiplying-time/?answer=194971#post-id-194971