# How to calculate time worked: Start time, end time, lunch break

1. ## How to calculate time worked: Start time, end time, lunch break

I have a sheet which has the start, stop and time taken for lunch breaks.

I want to calculate the total time worked such that i have the hours worked. So if i worked 7 hours and 30min then the total time would be 7.5hrs. But i am not sure how to take into account the lunch break time (see enclosed sheet).

anyone have any ideas?  Register To Reply

2. ## Re: How to calculate time worked: Start time, end time, lunch break

One way:

=G2-F2/60/24

Regards  Register To Reply

3. ## Re: How to calculate time worked: Start time, end time, lunch break

exactly what i wanted. thanks.  Register To Reply

4. ## Re: How to calculate time worked: Start time, end time, lunch break

You're welcome. Thanks for the rep.

Regards  Register To Reply

5. ## Re: How to calculate time worked: Start time, end time, lunch break

hi

i just tried the formula and it does not work on the spreadsheet from which the problem was posed.

am i doing something wrong in placing that formula in the total including lunch column?  Register To Reply

6. ## Re: How to calculate time worked: Start time, end time, lunch break

My interpretation is that the OP has calculated the total elapsed time between the start and end times. He presumably is not going to want a figure that adds the lunch break on top of that. Hence, the calculation subtracts the lunch break (minutes) from the total elapsed time.

The formula goes in cell H2 and is copied down. The value in cell G2 is the End Time - the Start Time. The formula, =G2-F2/60/24, converts the value (in minutes) in cell F2 and converts it into a time value, then subtracts it from the value in G2. Terminology feels wrong (IMO), but the answer posed seemed to be what the OP was looking for. At least, that's how I interpreted "exactly what i wanted. thanks." And, as far as I am aware, he has not come back to complain in the last 4+ years.

In what way do you mean "it does not work on the spreadsheet from which the problem was posed." What doesn't it do that you expect it to? Or vice versa?

As far as I am concerned, it does.  Register To Reply

7. ## Re: How to calculate time worked: Start time, end time, lunch break

Hi, my calculation is slightly different, I want to calculate the time spent on a task within a shift with a set break time, but when the tasks start after the break time the break is deducted resulting in a negative time. How do I rectify this?  Register To Reply

8. ## Re: How to calculate time worked: Start time, end time, lunch break

Welcome to the forum. We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread  Register To Reply