# Convert time to decimal hours

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.

1 Like

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.

TimeSheetJan16-Jan22.xlsx (6.3 KB)

Check the In-values!

3 Likes

Hallo
`E7:H7` is autoincremented by 1 (24hours) on each column.
Select D7hold `<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