0

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.

1 Answer 1

1

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.

5
  • 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, 2012 at 17:31
  • I did: =TIME(ABS(TRUNC(C36));MOD(C36;1)*60;0) and it works. Thanks.
    – vulkanino
    Jan 19, 2012 at 17:48
  • @vulkanino, I just added a simpler method to my answer. Jan 19, 2012 at 17:49
  • Isn't it just =D45/24-D44 - format as time Jan 19, 2012 at 18:49
  • @barryhoudini, that works too. I wasn't sure when I answered whether the result should be hours or a time. Jan 19, 2012 at 19:35

You must log in to answer this question.

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