Forum Discussion
Excel Formula
Hello All, I need some assistance,
I have a spreadsheet that I am trying to record accrued paid time off. We are paid bi-weekly so every 80 hours they accrue 2 hours. BUT the max per paycheck is 2 hrs. There for they may work 81 hours but will ONLY accrue 2 hours. How can I create a formula that I can enter over 80 hrs worked but it will ONLY give them 2 hrs accrued?
ex: 80hrs worked = 2hr accrued
ex: 85hrs worked = 2hrs accrued
In order to write a totally reliable formula, you'll need to be a bit more comprehensive with your description.
It may seem obvious to you, but relying on the "obvious" can be a recipe for disaster.
So what you're doing is recording hours worked, a part of which is paid time off accrued under certain circumstances. You've only given two, neither one of which is intuitively obvious. If 80 hours means 2 hours of accrued paid time off, what does 79 hours mean? 1 hour accrued? How about 79.5? Etc.
What about 90 hours? Still only 2?
And if they're under 80 hours, is nothing accrued?
Not to be difficult; just wanting to be totally reliable.
How is your spreadsheet for tracking this organized? Would you be willing to post a copy (removing real names) on OneDrive or GoogleDrive, with a link here granting edit access.
The formula you're seeking is not going to be difficult, but how it would be constructed depends on how you are collecting and organizing the time worked in the first place.
mathetes The formula I have for accrued time is hours worked multiplied by 0.025.
ex: 80:00*0.025=2:00
ex: 75:00*0.025=1:52
ex: 63:39*0.025=1:35
ex: 90:00*0.025=2:15 **but would like to cell to read 2:00
**The max accrued per paycheck is 2:00. If an employee worked 90hrs they will still only accrue 2:00.
If I enter my formula for 90hrs into excel it would be 90:00*0.025=2:15. But as stated they are only allowed to accrue 2:00 per check. There for I would like that cell to read 2:00.
I have the cells set up as followed;
F1 - hours worked
G1 - hours accrued (which currently has the formula, F1*0.025) and would like this cell to have a max total of 2:00 but to be able to show totals under 2:00.
Please let me know if that explanation was any better.
Thanks for your help!
If you don’t have a recent version of Excel, needed for the LET function, you could use this as an alternative
=MIN(2, F1*.025)
Thank you, though for a far more thorough explanation.
At the simplest level, if the hours worked is in cell A1, =IF(A1>=80,2,0) will give you the answer 2 for any number equal to or greater than 80.
As I said in my first post, though (and writing as a person once responsible for the HR/Payroll database of a major corporation) , I would not be satisfied with that simple picture. There are too many questions, too many slightly different circumstances or contingencies, not addressed by that formula.
