Need help Creating a formula

[Grettel_E]

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?

[#2]

@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.

[#3]

@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?

[#4]

@Grettel_E 

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.

[#5]

@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? 

[#6]

@Grettel_E 

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.

[#7]

@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. 

[#8]

@Grettel_E 

I just realized that I'd been misunderstanding your rule. I was thinking 1 sick day for every 30 hours, not reading closely enough that you were saying 1 sick hour for every 30 hours. So I've changed the wording in the headings, and suggest that when someone takes a sick day you record it not as "1" but as a number of hours. This would enable you to handle somebody falling ill mid-day, leaving at noon, and only therefore using 4 hours. Or just use 8 when they've taken a full day.

With regard to the extra column to track carry over hours, you are explaining yourself well. I may not have explained myself well enough, however.

The formula I gave you up at the top-- =INT(SUM(B5:B70)/30)--the formula that calculates Sick HOURS earned, already takes into account those carry-over hours. So an extra column would be redundant.

Try it out, Just enter 10 hours for one week, 50 for the next 30 for the next (or any other kind of sequence you'd like).....keep track manually and see if the formula doesn't give you the results you want. [Hint: it will.] How? Why?, Because it simply keeps a running total for all of the weeks in the range, divides that by 30 and then takes the integer portion of the result. But any "remainder" is naturally included in the next week's calculation.

But referring to that range (currently the entire column of pay weeks, which runs for two years) raises some of the questions I'd have based on past experience with Payroll systems

1. Is there (shouldn't there be) a maximum number of sick hours or days that any given employee can accumulate?
2. Do sick days earned in 2020 simply carry over into 2021 in their entirety? Or is there a reset of some kind? Assuming you don't reset to zero for a new year, do you reset to a base line number for those who have reached or exceeded that base line number?
3. Is there any place for Vacation Days in your process? How do they accrue?

You get the idea. A robust time tracking system would incorporate all such rules that a company has. Excel can handle a fairly complex set of rules, but they do need to be spelled out. Do you have such rules beyond the one that we've been dealing with so far?