I'm working with a time sheet where I need to calculate and track actual hours worked, comp hours earned, comp hours accumulated, and comp hours used. I have formulas to do this, but we are going to a new system where comp hours worked are earned at a rate of 1.5. I need to add up hours worked for the current week, multiply anything over 40 by 1.5, and add it to the "comp hours bank". But if a person uses comp hours instead of earns them, those just need to be subtracted out of the bank.
Is there a way to do this formula all in the same cell or do I need to create a reference cell?
I have a screenshot of my worksheet but I can't figure out how to insert it in this message. I will try and explain below. Where the text is red - those are the cells where I want a formula that will take current hours worked, subtract 40, multiply by 1.5 if it's a positive number, then add to or subtract from whatever was already in the bank.
Example:
<tbody>
</tbody>
Thank you for any help!!
Is there a way to do this formula all in the same cell or do I need to create a reference cell?
I have a screenshot of my worksheet but I can't figure out how to insert it in this message. I will try and explain below. Where the text is red - those are the cells where I want a formula that will take current hours worked, subtract 40, multiply by 1.5 if it's a positive number, then add to or subtract from whatever was already in the bank.
Example:
| Comp Hours Bank (carried over from previous month) | 3 |
| Week 1 hours worked | 50 |
| New comp bank total (week 1 hours - 40, multiplied by 1.5, added to 3 hours already in bank) | 18 |
| Week 2 hours worked | 32 |
| New comp bank total (8 hours comp time used, subtracted from bank) | 10 |
<tbody>
</tbody>
Thank you for any help!!
