Calculating regular/overtime hours

Hi,

I need some help calculating regular/overtime hours for a payroll spreadsheet. I am able to calculate total hours by inputting in time and out times but I am unable to figure out formulas to do anything else.

I have a column for lunch. If "y" is inputted in the column then .5 hours need to be deducted from total hours in a separate column, giving a value of total payable hours. I have columns for regular hours and over time hours. If total payable hours are over 9 then only 9 hours should be in regular hour column and remaining should automatically be inputted under over time hours. Will anyone be able to help me with a formula for total payable hours, regular hours and over time hours formulas?

I will make some assumptions here. In this example, cell A1 will contain the total hours and cell B1 will contain the “Y” for lunch. I am assuming this hasn’t already been deducted from the total hours and that normal time is 9 hours, not including lunch (i.e. 9.5 hours in total)

In the “Normal Hours” column, enter

Formula:

Please Login or Register  to view this content.

In the “Overtime Hours” column, enter

Formula:

Please Login or Register  to view this content.

Thank you very much buzzbee, it worked. I was getting a circular reference error originally and I still got that with your formula. Initially I had thought that there was something wrong with the formula but I was referencing O/T column in Total Hours column for some reason. I fixed it. Now the only problem I have is that if no in/out times are entered, both Reg and O/T columns return "####" as results. How may I incorporate ISERROR into the two formulas you provided so the results for both are blank cells if there is an error?

I'm not the greatest with the ISERROR, so I would usually just use this...

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

This simply says if no hours are entered, leave the field blank

Thank you