Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .