# excel time difference if a cell is not formatted as time

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.

-

Isn't it just `=D45/24-D44` - format as time – barry houdini Jan 19 '12 at 18:49