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.
Best Answer
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.