Excel 2007 : Calculations on a cell containing hours and minutes

[bbs10r]

I'm working on a time sheet application. I have the start time and end time. I also have a place for "non billable time". That would include lunch or any other time off.

I want to be able to calculate the time difference between start time and end time AND subtract the non-productive time from it.

I have no problem with the calculation of hours and minutes between the start time and the end-time. But when I try to use the non-billable time in any calculation, it gives me a #VALUE error.

Can anybody help?

[wayneg]

enter the time to reduced in a cell then just add the cell to the formula do delete the non billable time

[bbs10r]

I've tried that any number of ways. The non-billable time is defined as time type 13:30. When you enter the :30 into the non-billable time, it gives the #VALUE error. I've tried defining it as custom [h]:mm for instance and it gets the same result. I attached the spread sheet to my original post. Try to use it by putting some value into cell B14 and see what happens. I've tried entering the half hour as .5 but Excel translates that into 12:00 as in a half of a day.

So I'm still asking for help.

[oeldere]

See row 22 (see the example).

[bbs10r]

OK. This works. But so sum up, the secret is in how you enter the non-billable time. ":30" doesn't work for a half-hour. But if you enter it as "0:30" it works. Internally, it thinks you are entering 12:30 am, but it does calculate properly.

Thank you so much. That was a solution I had not tired.

bbs10r

[oeldere]

Please Login or Register  to view this content.

That was a solution I had not tried?.



See row 22 (orange cells) (see the example).

[bbs10r]

Indeed. That's what we call a "fat finger". Tried was the word.

bbs10r

[oeldere]

And the change of formula in #6.