Time sheet formulas

[srogers11]

I'm working with 2007 version and my knowledge of formula's is limited. I need help writing a formula to calculate extra time worked. I have a formula to calculate total time worked and compensatory time (overtime that's put in as leave not paid). I need a formula to calculate extra time (time that will be paid in cash that's seperate from the comp time). I've attached a copy of the current sheet. The sheet I have works for hours over the threshold but does not work to populate the extra time when an employee does not work a full pay period.

Thank you in advance.

[Pepe Le Mokko]

Hi and welcome to the board

Just to make sure we understand correctly your requirement, could you please add an example of your problem to your sheet?

[Pepe Le Mokko]

Duplicate post

[srogers11]

Thank you for the help. I've attached a sheet which shows a person worked 152 hours of regular work and twenty four hours of extra work. I49 should pull in the calculated overtime from H54 if there is any. In this example there is no overtime. If there is not any overtime it should only show what is in C42. That whole row in 54 can be reworked I'm not happy with what it does, occasionally it will put in hours in I49 when it shouldn't.

[FDibbins]

If all you want to do is "populate I49 with either C42 or H54, then this will work...

=IF(H54=0,C42,H54)

I have cleaned up some of the formulae in your last table.

If I didnt understand your requirement properly, please try to explain again

[srogers11]

Thank you for your help.

"If all you want to do is "populate I49 with either C42 or H54, then this will work..."

This has been helpful but I need I49 to do more than just populate with those cells. I've attached the corrected form with explanations.

[srogers11]

Don't mean to sound impatient. Does anyone have any suggestions on how I can get this formula to calculate the data?

[FDibbins]

Sorry for the delay, I was off for a few days...working on your solution right now

[FDibbins]

Ok, for the 1st part of your question, i changed I49 to...

=IF(AND(H54>=1,H54<=23),C54-A54,IF(H54=0,C42,H54))

This works (as far as i can tell) to give you what you need to a 0, 1-23 and 24 in H35.

Now, for the 2nd part of your questiom I was a little unclear as to which cell you wanted "a function that adds C54-A54 without becoming a negative" to be?

[srogers11]

It's taken me a few days but I think I have the question to the formula finally figured. What I think I need is the following formula: If C42 + F41 added together is equal or less than 168 then I 49 should = C42. If F41 + C42 added together is above 168 but F41 is below 168 than I49 should = H54 + 168 - A54. If F41 is equal or above 168 than I49 should = H54.

Thanks you again for the help.