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.

I just can't solve this! Excel 2010, I have a timesheet where I write day by day the entry time, exit time, lunch time. A cell computes the worked hours, say:

A       B       C        D
entry   exit    lunch    worked(=B2-A2-C2)
09.30   18.20   00.15    8:35
09.20   13.10   00.00    3:50
09.30   18.40   00.45    8:25
09.15   18.40   00.30    8:55
...

Now I sum the worked day and times in the summary cells:

Worked days (=COUNT.NUMBERS(D1:D31))    13 
Worked hours (=SUM(D1:D31))             99:05  <- formatted as [hh].mm
Full hours (=D33*8)                     104    <- hours if I had worked full time each day
Difference (???)

In the Difference cell I want to know how many hours I have worked more (or less) than the standard 8 hours per day.

A simple difference =D45-D44 does not work, because the two cells are in a different format. I've tried a lot of conversions and cell formats, with no luck.

The funny thing is that with the previous excel version (2007) it worked! I can't figure this out, thanks.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Times and dates in Excel use a format where 1.0 represents a day. Take the cell that's formatted as hh:mm and multiply it by 24 to convert it to hours.

To convert the hourly result back to a time, reverse the process - divide it by 24.

share|improve this answer
    
Multiplying a 99:25 cell by 24 I get 99.42 (in decimal representation). The difference with 104 is 4.58 and it is quite right! But it's in numeric format, how do I convert 4.58 into a [hh].mm format so that I get the right 04:35 ?? Thanks. –  vulkanino Jan 19 '12 at 17:31
    
I did: =TIME(ABS(TRUNC(C36));MOD(C36;1)*60;0) and it works. Thanks. –  vulkanino Jan 19 '12 at 17:48
    
@vulkanino, I just added a simpler method to my answer. –  Mark Ransom Jan 19 '12 at 17:49
    
Isn't it just =D45/24-D44 - format as time –  barry houdini Jan 19 '12 at 18:49
    
@barryhoudini, that works too. I wasn't sure when I answered whether the result should be hours or a time. –  Mark Ransom Jan 19 '12 at 19:35

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.