Excel Working Time Calculation

snips86x · 26 Aug 2014 at 09:34

Not great with Excel, so I apologise.

This is the formula I'm using to calculate the working hours between a given start and finish time.

I need to adjust this deduct a 30 min break and my googlefoo has failed me

How would I amend it?

=SUM(C7-B7)

*C7 indicates the start time and B7 indicates the finish time leaving me with the total worked hours.

Columbo · 26 Aug 2014 at 10:12

-0.5?

snips86x · 26 Aug 2014 at 10:18

I did try that, but it threw the forumula our of kilter and game me #########

pepp77 · 26 Aug 2014 at 10:25

What format are you using for time

8.5 or 08:30 for half eight?

snips86x · 26 Aug 2014 at 10:26

pepp77 said:
What format are you using for time

8.5 or 08:30 for half eight?

08:30 for AM and 17:00 for PM, but it converts it to 12 hour, so 5:00 PM

pepp77 · 26 Aug 2014 at 10:31

Create a cell with 0:30 in it. Then in your calculation do a minus then point to this cell for the half hour reduction.

snips86x · 26 Aug 2014 at 10:38

pepp77 said:
Create a cell with 0:30 in it. Then in your calculation do a minus then point to this cell for the half hour reduction.

Not sure why I didn't think of this considering 0.5 didn't work.

Thanks, it's calculated it for me

Chandler_90 · 26 Aug 2014 at 12:02

I used the following:

Cell B2 has the start time in using 24 hour clock
Cell B3 has the finish time in using 24 hours clock
Cell B4 has the lunch duration in, in this case 30 mins is 0.5.

Formula used is:

=SUM(B3-B2)-B4

This seems to work as long as the hours are within the working day.

So, Start time of 9 and an end time of 17:00 minus 30 mins results in 7.5.