Forum Discussion
Need help Creating a formula
What formula can I use to create a payroll sheet/ sick time sheet. I need to create a cell that will allow me to add 1 sick hour for every 30 hours worked. So I will have a column that will add the hours weekly but I need a formula that can help me add with ease. Can someone help me with this?
7 Replies
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.
