Open Office Calc time to decimal

My task is very simple but I cannot figure out how to do it.

I am setting up a time sheet in OpenOffice Calc with 3 columns. The first two columns are Time formatted columns that represent my start and end time. I want the third column to show the difference in hours as a decimal. For example, this is my desired display:

| 9:00 AM | 12:30 PM | 3.5 Hrs |

I think I need a combination of a function and formatting, but I just can't make it work.

-

Try this:

``````A1        B1         C1
9:00 AM   12:30PM    =(B1-A1)*24
``````

Should see:

``````A1        B1         C1
9:00 AM   12:30PM    3.50 Hrs
``````

Make sure you format C1 as a Number, with whatever desired decimals (I usually use 2).

To get the "Hrs" to show up, make the format code, which is the 0.00 shown in the bottom of the formatting numbers box, into "0.00 Hrs" (quotes included) It'll change the format to "User-Defined".

That should do that you want.

time is stored internally as {whole_days}{decimal_point}{fractional day}

So 1 day (24 hours) = 1.00

12 hours = 0.50

3.5 hours = 0.15 (actually: .14583333 = 3.5/24 = 7/48)

Hope this helps.

-

This doesn't work for me...

I've also tried =HOUR(C5-B5)

This gives me the hours but leaves out the decimals. I have the column/cell formated to number with 2 decimals.

So If I worked 2.5 hours, 9:30-12:00 it shows a 2.

Any ideas?

-
Please post a new question. The space you typed into was for answers only. – Lee Taylor Jan 12 '13 at 3:16
You could have also edited the original question post, and added what you typed here there, and then written a comment to the answerer.. Anyway - what I did was to format the time cells as `[HH]:MM` in Format cells/Format code (as per Documentation/How Tos/Calc: Date & Time functions - Apache OpenOffice Wiki); then after you enter the formula (and get the 2), right-click and "Clear Direct Formatting" - this should give you a floating point number (0.1041666667); now ... – sdaau Feb 23 '14 at 18:46
... having selected the result cell, click on the formula entry again; and now add the `*24` to, say, `(D7-C7)` - and now you should (hopefully) get the result with decimals; after this, you could re-format the number with the proper amount of decimals, I guess. Hope this helps - cheers! (EDIT: may have forgotten to say, that at first, I didn't enter the whole formula (with *24), just the difference `(D7-C7)`). – sdaau Feb 23 '14 at 18:50