Excel 2007 : Spread of Hours Calcuation

**Lfrat** · 06-07-2012, 03:41 PM

I have created a time sheet based on a 10 hour day and I've successfully incorporated the overtime rates for 1.5x and 2x regular pay. We pay 1.5x for hours 11 and 12 and 2x for hour 13 and beyond.

I already have the sheet calculating the OT pay based on the hourly rate *1.5 or 2.

However, I still need to manually enter the number of OT hours in the Rate 1 column and the number of OT hours in the Rate 2 column.

Is there any way to get Excel to look at the total hours in Column A, and then understand that if that number is 15 for example, 2 hours need to go in Column B (Rate 1) and then 3 hours need to go in Column C (Rate 2)?

Thanks!

**amotto11** · 06-07-2012, 03:46 PM

Look at my spreadsheet and let me know if this is what you are looking for

**Lfrat** · 06-08-2012, 08:11 AM

Hi:
When I opened the sheet, there didn't appear to be anything there...can you re-post?

**amotto11** · 06-08-2012, 08:42 AM

I just opened it and it was there for me. You can recreate my sheet by just putting 15 in cell A1, then put this formula in B1: =IF(A1>10,IF(A1-10>2,2,A1-10),0), and this formula in C1: =IF(A1>10,IF(A1-10-B1>0,A1-10-B1,0),0) Let me know how this works for you.