Sick time accrual with previous year carry over.

**allprovirgil** · 01-27-2023, 05:25 PM

I have the attached workbook and it will accrue our time correctly. We earn .02hrs per hour worked in a work week. with a max allotment of 40hrs per calendar year. We can carry over what is not used to the following year but you still can not exceed 40hrs. Is there a way to account for the carry over from the previous year. i created a master sheet if any changes could only be made on that i will apply to all other sheets in the workbook. Any assistance would be greatly appreciated. Sick time calculator.xlsx

**JeteMc** · 01-30-2023, 11:25 AM

Perhaps this will help:
1. Populate column D using: =SUM(D4,C5)
2. Populate column F using: =SUM(F4,E5,IF(ROW()=5,F3,0))-SUM(G4)
3. Populate column H using: =IF(F5-G5>40,40,F5-G5)
For future reference please provide at least some manually calculated examples of your expected results.
I also suggest that you do not include protected sheets in your sample workbook.
Let us know if you have any questions.

**allprovirgil** · 01-30-2023, 02:46 PM

Thank you for the help on this. Sorry i thought i had unprotected the workbook before uploading. IN the future i will post expected results this was my first time posting things to learn. Thanks again.

**JeteMc** · 01-30-2023, 07:09 PM

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

**allprovirgil** · 01-31-2023, 05:21 PM

IN Cell H38 it goes over 40hrs earned how do i have it not go over 40 and the additional cells below equal out to zero. I know i need a IF statement but how does that work with a Sum Statement as well.Sick time calculator.xlsx

**JeteMc** · 01-31-2023, 07:49 PM

Try pasting the following into cell H5 and then double click the left mouse button to copy down:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**allprovirgil** · 02-01-2023, 09:20 AM

Final question I had the wrong sheet attached after your previous help. is there a way to make H39 to H56 show zero as they can not accrue anything over 40hrs. Sick time calculator.xlsx

**JeteMc** · 02-01-2023, 09:46 AM

Try the following:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**allprovirgil** · 02-01-2023, 10:03 AM

That did not make H39 to H56 become zero. please see attached.Sick time calculator.xlsx

**JeteMc** · 02-01-2023, 11:26 AM

Try the following:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**allprovirgil** · 02-01-2023, 12:09 PM

One finial question. J5 only shows the carry over if there is a input put into E5. Is there a way to have H3 populate to J5 without any input into E5.Sick time calculator.xlsx

**JeteMc** · 02-01-2023, 02:03 PM

Try the following:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**allprovirgil** · 02-03-2023, 09:27 AM

My conditional formatting is not working. Column J is supposed to Light fill red with Dark red text if the value of that cell equals zero. thanks for advice.Sick time calculator.xlsx

**JeteMc** · 02-03-2023, 10:55 AM

Try Changing the formula in column J to read:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**allprovirgil** · 02-03-2023, 10:58 AM

I tried that at it started messing with the hours that have been accrued it added more to it then what it should have if i follow it down the column.

**JeteMc** · 02-03-2023, 06:50 PM

Please provide an example illustrating the issue.

**allprovirgil** · 02-07-2023, 08:44 AM

Sorry for the delay. attached is the sheet. and it added sick time used to column fSick time calculator.xlsx

**JeteMc** · 02-07-2023, 10:01 AM

We are still not seeing expected results; however, I will guess that the following two changes do what you want:
1. For column F: =SUM(C5:E5)
2. For column J: =ROUND(SUM(IF(ROW()=5,H3,0),IF(SUM(J4)=40,0,MIN(40,SUM(H5,J4)-I5))),2)
Let us know if you have any questions.

Sick time accrual with previous year carry over.

LinkBack

Thread Tools

Rate This Thread

Display

Sick time accrual with previous year carry over.

Re: Sick time accrual with previous year carry over.

Re: Sick time accrual with previous year carry over.

Re: Sick time accrual with previous year carry over.

Re: Sick time accrual with previous year carry over.

Re: Sick time accrual with previous year carry over.

Re: Sick time accrual with previous year carry over.

Re: Sick time accrual with previous year carry over.

Re: Sick time accrual with previous year carry over.

Re: Sick time accrual with previous year carry over.

Re: Sick time accrual with previous year carry over.

Re: Sick time accrual with previous year carry over.

Re: Sick time accrual with previous year carry over.

Re: Sick time accrual with previous year carry over.

Re: Sick time accrual with previous year carry over.

Re: Sick time accrual with previous year carry over.

Re: Sick time accrual with previous year carry over.

Re: Sick time accrual with previous year carry over.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] sick time accrual

Vacation & Sick Time Accrual Help

Assistance with calculating sick time accrual

[SOLVED] Timesheet vacation & sick time accrual

Formula for Calculating (Accrual) Vacation Time/ sick time

vacation accrual formula based on previous year avg hours worked

template to track sick and vacation time for a year

Bookmarks

Bookmarks

Posting Permissions