Forum Discussion
Need help Creating a formula
Grettel_Ea ca
I'm sure there are folks here who'd be happy to help, me among them. And it's probably not all that difficult.
It IS difficult, however, to suggest a formula (or approach) without knowing how you already have structured your spreadsheet to track hours worked. It also wouldn't make sense for any of us to create one from scratch, since it might not reflect your reality. So, yes, I am assuming you have something you're working with now. If not, there are surely templates available, either here on the Microsoft site or elsewhere on the internet.
So here's the request: please post a copy of your spreadsheet as it currently exists. Just make sure that no real names appear, so make it a copy and substitute Disney character names (or Star Wars, if you prefer).....similarly any other confidential or private info (SSNs, phone numbers, etc) should be removed.
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.