Forum Discussion
Need help Creating a formula
mathetes this is what I have and for some reason it has created a mess of a spreadsheet. Column C needs to accumulate 1 hour for every 30 hours worked (column B), Column D "total accrued" may not be needed.. I would like Column E to show a total of hours if employee uses 6 hours or 8 hours I would like it to show somehow. Column F should only show the left over time (this is just as a personal reference) If employee worked 30 hours this week 40 hours the next I should be able to carry over 10. Am I explaining myself? I think this is where I'm getting lost with the template.
Is this something you can help me with?
I think the attached may resolve your questions. You posed a point I was about to ask you, when you said, "If employee worked 30 hours this week 40 hours the next I should be able to carry over 10." I was going to ask whether or not three weeks in a row of 40 hours wouldn't mean accruing 4 sick days (120 hours divided by 30). I'm taking your question to mean that IS the case. And if that is indeed true, you don't need your week-by-week IF function. All you need to do is track total hours worked and divide by 30 to get total sick days accrued. Then subtract Sick Days actually used, to yield the Balance still available.
This formula, with INT making sure it's a full integer (rounded down, in effect).
=INT(SUM(B5:B70)/30)
placed in your cell I1 yields the total Sick Days earned
Cell !2 shows used
Cell I3 shows balance remaining
You don't need to track things in those two columns C and D any longer, doing it this way. Just in column E, where you record Sick Days used.
As for your request to show 6 or 8 hours (I assume you mean for sick days used), if 1 full day equals 8 hours for all employees, then you could simply record 6/8 for anyone who only used 6 hours of sick time. So this really gets at a deeper question: are you tracking Days or Hours? Are all employees Full-time (8 hours/day) or are some part-time? Do any work over-time, and if so, do those hours qualify toward the 30 hours earning a Sick Day? Etc.
In other words, we may need (you may need) to define the rules a bit more precisely. The formulas aren't hard once the rules are known.
mathetes Ultimately yes we are tracking days but need to record it as hours. For every 30 hours worked and the employee will bank 1 sick hour. Yes, the employee can work over 40 hours and keep accumulating sick time. Part timers and full timers (40 hours) will earn the same amount of sick time since it is earned by every 30 hours worked 1 hour sick time is made.
Does this help?
Does this help?
Well, candidly, yes and no.
- Yes, in that the basic spreadsheet I sent back (I think) does that. [You could provide some feedback concerning that.]
- No, in that there are potentially a LOT more questions. I was just giving some examples. [In my pre-retirement days, long long ago, I was the director of the HR/Payroll database for a major corporation, so I have been exposed to some of the rules that can exist around this on-the-surface "simple" matter.]
So at this point I'm just wondering what you thought of the spreadsheet I posted. It was an initial attempt to accomplish what I understood you to be seeking. If it does but needs refinement, please spell out what that refinement needs to be. If it doesn't and needs total re-work, same thing: spell out what's needed.
mathetes Yes the template you did is excellent is short and just what I need but I did want to create another column that keeps tally of the extra hours towards the accrued sick time. If an employee worked 20 hours this week and 30 hours the next.... a column might be a good idea to show that the balance of 20 hours worked.... and so forth.
Then the following week the employee works 40 hours than that balance becomes 0. (Rule= 30 worked hours = 1 sick hour)
Also I definitely am going to use the top of your spreadsheet it clearly shows what I need right there.
I don't want to complicate this sheet and yours is simple but useful. My only suggestion is to possibly add that column to track the carryover worked hours towards the next earned sick hour.
Please let me know if I'm explaining myself correctly. Thank you.