I have a total time of 15:30 (15 hours and 30 minutes, or 15.5 hours). I need to multiply that by an hourly rate of £10, to give a billable amount of £155. I cannot find a way to do that. Any suggestions?
Hello,
assuming 15:30 is in cell A1, hourly rate 10 is in B1 use: =(A1*24)*B1
Background: Time in calc is a fraction of a day, thus multiplying a time by 24 yields the decimal number in hours (See also OASIS Opendocument standard - 4.3.2 Time).
Note: Don’t forget to format cells using time differences with format code [HH]:MM, which allows more than 24 hours.
Hope that helps.
If the answer helped to solve your problem, please click the check mark (
) next to the answer.
Perfect, exactly what I needed. Thank you.
Hello, I don’t have that luck. Here is what I get when multiplying with 24:
313:51:40 goes to 7532:40:00
What is the problem? I use Libreoffice 7.0.0.3
OK, I found out. It seems that the time should be in HH:MM:SS format, not in [HH]:MM:SS.
OK, I found out. It seems that the time should be in HH:MM:SS format, not in [HH]:MM:SS.
You probably don’t want that since HH:MM:SS format shows modulo 24 and not a whole time difference, which could be larger than 24 hours. And 313:51:40 * 24 = 7532:40:00 is absolutely correct:
313 hours * 24 = 7512 hours
51 minutes * 24 = 1224 minutes = 20 hours + 24 minutes
40 seconds * 24 = 16 minutes
Finally we have: 7512 hours + 20 hours + 24 minutes + 16 minutes = 7532:40:00
My issue is a complete miscalculation. The hours are correct, for the first 3 entries, but they suddenly become a negative number, which is erroneously larger, with each subsequent entry. The formulas are 100% correct. Every other cell balances.
Thanks!
@larashaw73 Share a Calc document, a screenshot is worthless in this case.
Helpers have to check formulae and/or formats.
Hallo
E7:H7 is autoincremented by 1 (24hours) on each column.
Select D7 … hold <ctrl>key and pull right to H7
1 Like
That changed absolutely nothing. I’ve checked the formulas. All times are military (24 hr). Cells B17:D17 are all correct. If I drag the formula across Row 17, cells E17:H17 all display incorrect numbers.
See above screenshot, for times and hours. Forum won’t let me re-post that, to compare to this one, with formulas shown.
So (change NumberFormat to DD.MM.YYYY HH:MM:SS):
E7: 31.12.1899 08:45:00
E8: 30.12.1899 14:30:00
E7>E8
@larashaw73 As @karolus pointed out it is not the formulae but the initial values of time in the first In row (cell addresses E7, F7, G7, H7). You may inadvertently have added the autoincremented 1. The formulas are correct but not the subtrahend.
You calculate:
E8 - E7
14:30:00 - 32:45:00
The difference is a (negative) period.
Hit shortcut CTRL+M to display the real time values as figures. See attached screenshot.
.
1 Like
Actually not 24 hours. Your formatting hides values greater 1 as this thread already tells:
So try [HH]:MM:SS as formatting and accept formatting does not change a value, but can hide it from the eye.
It works! Thanks!!