Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

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.

share|improve this answer
add comment

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?

share|improve this answer
1  
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 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 at 18:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.