Re: [libreoffice-users] Re: Adding decimal minutes to regular minutes and hours (calc)
So if I'm getting this right, I would just do something like this: A B 1 Last Call: 5:58:16 2 Duration: 6.24 Total:: =B1 + B2 (and format as needed) On 4/6/2016 8:07 AM, Andreas Säger wrote: Am 06.04.2016 um 15:36 schrieb John Meyer: Hi, I have a project where I am trying to calculate the total amount of work hours for a project based upon the timestamp of calls throughout the day. The time at which the calls is placed is in hh:mm:ss while the duration of the call is in decimal time. I need to take the last call of the day and add the decimal time to get an accurate shift time.Do I just put the hh:mm:ss time in the time function and add that to the decimal time? Thanks. Spreadsheet dates are formatted numbers in unit "Days". 0.0 <==> 00:00 0.25 <==> 06:00 1/4 of a day 0.5 <==> 12:00 1/2 of a day 0.75 <==> 18:00 3/4 of a day 1.0 <==> 24:00 (with number format [HH]:MM) There is no difference between the day fraction on the left and the formatted number on the right. Both sides yield the exact same results for all kinds of calculations and comparisons. Multiply by 24 in order to convert days to hours and switch to some decimal format when the result looks wrong. Number format [HH]:MM displayes hours beyond 24 as full hours. This number format should be used when you want to express time spans in hours. A time span of 2 days and 3 hours (value 2.125) formatted as HH:MM shows 03:00 (the hours at a point of time) whereas [HH]:MM shows 51:00. The first method actually calculates the amount of hours, the formatting solution provides a more adequate representation of time spans without any conversion. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Adding decimal minutes to regular minutes and hours (calc)
Am 06.04.2016 um 19:27 schrieb James Knott: > > While I haven't done this in a spreadsheet, I used to on a calculator. > I did it by converting between decimal degrees and degrees, minutes & > seconds. > > BTW, as an indication of how long ago I did this, I used this method to > add up the time for tracks on an LP to figure out how much time I'd need > on a cassette. ;-) > > You could have used a spreadsheet back in the 80ies and it would be the exact same method. Either multiply the days by 24 to get hours or change the number format. As a third method you may separate full days from day fractions. A1: =PI() =int(A1) => 3 full days =mod(A1;1) => 3:24 (about 1/7 of a day) A1 formatted as HH:MM => 03:23 (time on day #3) A1 formatted as [HH]:MM => 75:24 (showing full hours) =A1*24 => 75.4 hours -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Adding decimal minutes to regular minutes and hours (calc)
Am 06.04.2016 um 15:36 schrieb John Meyer: > Hi, > > I have a project where I am trying to calculate the total amount of work > hours for a project based upon the timestamp of calls throughout the > day. The time at which the calls is placed is in hh:mm:ss while the > duration of the call is in decimal time. I need to take the last call > of the day and add the decimal time to get an accurate shift time.Do > I just put the hh:mm:ss time in the time function and add that to the > decimal time? > > Thanks. > Spreadsheet dates are formatted numbers in unit "Days". 0.0 <==> 00:00 0.25 <==> 06:00 1/4 of a day 0.5 <==> 12:00 1/2 of a day 0.75 <==> 18:00 3/4 of a day 1.0 <==> 24:00 (with number format [HH]:MM) There is no difference between the day fraction on the left and the formatted number on the right. Both sides yield the exact same results for all kinds of calculations and comparisons. Multiply by 24 in order to convert days to hours and switch to some decimal format when the result looks wrong. Number format [HH]:MM displayes hours beyond 24 as full hours. This number format should be used when you want to express time spans in hours. A time span of 2 days and 3 hours (value 2.125) formatted as HH:MM shows 03:00 (the hours at a point of time) whereas [HH]:MM shows 51:00. The first method actually calculates the amount of hours, the formatting solution provides a more adequate representation of time spans without any conversion. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
