Time Sheet

**CambridgeNyc** · 07-31-2010, 12:31 PM

Ok, so I was trying to search this but did not know where to beging due to how complex it is.

What im trying to figure out is how to set up a complex time sheet that will break down a days total over hours, and make a total, so here is the situation,

Date / Time In Date / Time Out Total Hours 8 Hours Upto 12 Hours Over 12 Total
7/30/10 2:00 PM 7/31/10 1:00 AM 11.00 $100.00 $62.50 $162.50
7/31/10 6:30 AM 7/31/10 4:30 PM 10.00 $100.00 $31.25 $131.25

Ok so I figured out how to set it up so that it totals my time in and out to give total ours so up to 8 hours of work I get 100 dollars, so, anything after that up to 12 hours is 31.25 and hour. So I made the formula (total hours - 9)*31.25. that gives me the total. The minus nine is for 1 hour lunch. Now this is where I get stumped. I dont want the upto 12 hours colum to do more then 4 hours of totaling, cause then I would be over 12 hours, so I want only 4 hours to be totaled in that colum, if they were worked, then anything over 12 to go into the over 12 hours colum and then be charged at 45.25 and hour.

So the final product, all I would have to do is punch in the time in and out and it would do the math, looking like

Date / Time In Date / Time Out Total Hours 8 Hours Upto 12 Over 12 Total
7/30/10 2:00 PM 7/31/10 6:00 AM 11.00 $100.00 $93.75 181.00 $374.75

If anyone could shed some light on this complex issue!!

Thanks

*EDIT*
Ok sorry, I attched where I have gotten to on the workbook, its not very far

**royUK** · 07-31-2010, 12:32 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**Vinodsralian** · 07-31-2010, 01:12 PM

Hi,
Find attached, Hope it helps!!!

Vinod

**CambridgeNyc** · 07-31-2010, 01:36 PM

Thanks that was 3/4 of the way there after seeing how the If then function worked I was able to change it so that it put in proper hours!! Thats awesome thanks a million, I will have a few hairs left tonight that I wont pull out!