sick time tracker with cap on maximum accrued plus sick time used

Sampleworkbook.xlsx

Hi I recently noticed that the sick time tracker my daughter's small business is using has a big flaw. Employees accumulate sick time at a rate of 1 hour for every 30 worked and there's a cap of 70 hours. With the current spreadsheet they're using it doesn't cap the hours so people can accrue more than 70 hours. I've tried a few things but I'm not very good with Excel. I tried =MIN(70, ()) but then past a certain amount of hours, the "hours accrued" column just stays at 70 and people no longer accrue sick time. Right now in the "hours accrued" column I have to manually add the total hours worked. Can anyone help?

Maybe something like this in C2, copied down?

=MIN(70, A2-B2)

There may be good reasons why you can't do the following, but without knowing anything else about how you're using it, I would suggest using a Table for something like this. Entering in total hours in 1 column and calculating the earned sick time in another, etc. Maybe something like the attached.

Hi thank you for taking the time to answer. I'm tracking sick time accrued and used. The problem here is that if the hours accrued are over 70, for example 80, the balance column would show 70(correct) but if the employee uses 5 hours, it would still have a balance of 70. I need it to deduct from the balance even if it's at the max which is 70 so that it can then start accruing again next time I add hours to the hours accrued column. Maybe if the hours used column were a number(actual hours used) plus any amount over 70 in "hours accrued" column. That would deduct the correct amount off the balance column. It sounds convoluted so I think there is a better way but I'm just not familiar enough with this.

Maybe you should create a sample of your EXPECTED results. Are you saying that if you have accrued 70 hours of sick time, then work 30 hours more, you have accrued 71 hours of sick time but you only want to show 70? So if I then use 1 hour of sick time, it will still say 70? If this is correct, then the max of 70 doesn't really mean anything, does it?

I might need to start over with a whole new sheet, this is just what was provided to me when my daughter asked me to help. Basically employees accumulate sick time at a rate of 1 hour for every 30 worked. They can accrue 70 and carry that much over from year to year. Once they accrue 70, accrual stops until they dip under 70 again. I add the total hours worked every week to a running total so that for example someone that worked 1000 hours do far and works 40 more has 1040/30=34.7 hours total of sick time. As it stands I just don't know how to stop accruals at 70 and restart it again anytime it dips under that number.

I don't know why the file I attached in post #2 wouldn't work for you based on what you are saying. It seems to do exactly what you're asking.