Calculate Time worked

[jswanson1]

I know this question is all over excel forums but I couldn't quite find one for my needs. I am looking to see how much time was worked (or missed more importantly) that includes times for lunch. Most formulas i've found assume there is a fixed time for lunch. Below is an example of data I would be using.


Start 7:38 AM
Lunch Start 11:30 AM
Lunch End 1:05 PM
End 4:30 PM

[Pete_UK]

Assuming those times are in cells A2 to D2, you can use this:

=(D2-C2+B2-A2)*24

Format the cell as number with 2 dp, and the answer will be in hours.

Hope this helps.

Pete

[Kevin UK]

Hi jswanson1

Assuming your data is in A1:D2. In E2.

1. For time: =MOD((D2-C2)+(B2-A2),1) format cells as [h]:mm. The MOD is if the times are span 2 days ie: Start 18:00 & Finish 07:00
2. For decimal time: =MOD((D2-C2)+(B2-A2),1)*24, format cells as number & 2 decimal places.

[jswanson1]

Wow thank you guys! If I would have known how quick the response was I wouldn't have dinked around with it until 4:58 on a Friday!

[Kevin UK]

Hi jswanson1

Thanks for the feed back.