Excel Timesheet Calculation

Murray Brown
Murray Brown used Ask the Experts™

I use the following formula to calculate the hours worked between two times
=TEXT(C2-B2, "h:mm")
I now want to include a further cell for the time taken for lunch
How should I format that cell and what formula do I use to subtract
this break time from the result of the formula above
So if cell C2 has 6:00 and cell Be has 21:00 and =TEXT(C2-B2, "h:mm") used in cell D2
shows a result of 15:00. What should I out in cell F2 to show one hour for lunch and then
what formula should I put in G2 to show the final hours including the lunch break?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst
Why are you converting to text, it makes the calculations more complicated.

All cells formatted as h:mm

Start time B2:    06:00
Finish time C2:  21:00
Worked Time D2:  =C2-B2
Lunch F2:           01:00
Result G2:          =D2-F2
Analyst Assistant

You need to be careful when using the TEXT function.

The TEXT function turns a value into text so it can't be used directly in any other calculations/comparisons.

You can probably do what you want without the TEXT function.

In D2 put the formula =C2-B2.

In G2 put the formula =D2-F2.

That's all you should need if you are only entering times in B2, C2 and F2.

If you are including dates and/or the start/finish times may cross over midnight things can become complicated.
Roy CoxGroup Finance Manager
Here's a simple example
Murray BrownASP.net/VBA/VSTO Developer


Thanks very much
Roy CoxGroup Finance Manager

Pleased to help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial