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

-

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.

-
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