Timesheet that calculates overtime for various conditions

Hello all,

I am struggling to construct a timesheet that calculates overtime under various conditions: My biggest struggles are with C and D.

A: 1.5xOT for first 2 hours worked after a regular 8 hr workday

B: 2.0xOT for all additional hours worked after 10 hrs on a regular workday

C: OT for any hours worked outside of 0800 and 1600, with the first 2 hours being 1.5xOT, and the rest being 2.0xOT (eg. if you start work at 7am, then the first hour would be 1.5XOT, and then if you work until 7pm, then the first hour past 4pm would be 1.5, and then from 5-7pm would be 2.0xOT. And, if you start at 7am and only work a regular 8 hour day, then you still get overtime for that first hour).

D: All weekend hours are 2.0xOT

I am comfortable with IF, MIN, MAX functions, etc., but I'm new to working with times and dates in Excel, so those conversions are also tricky. Right now I am entering start and end times as 0:00, and looking to output hours worked as general numbers. Any help would be greatly appreciated!

Cheers,
Kash

Download Personnel_Timesheet_02_28_2014 from the site below to see if this would be helpful.

HTML Code: