Open Office Calc time to decimal

Question

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.

Answer 1

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.

Answer 2

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?