Hello everyone,
I am new to excel and just know how to use some of basic formulas.
So I created work book for bonus structure and I am stuck one formula. Work book has 12 sheets, one sheet for each month of the year and each sheet has two cells for first and second pay period of the month. To explain a bit more company pays 3 sick days in a year and they don't affect bonus. So lets say person has 1 sick day in first pay period of January and 1 sick day in second pay period of January. Formula will have to add them to holiday days in those periods. Lets say that same person has 1 more sick day in May, formula will add that sick day to holiday cell of the same pay period. Since person used his 3 payed days any other sick days will not be counted how ever we want to be able to keep adding them so we have total of sick days on the end of the year. Other example if person has 4 sick days in row formula will have to add 3 sick days to holiday cell and not count the 4th one or 5th one depending on how many days in a row did they have. . Other example: if person has 2 sick days in same pay period formula will add them to holiday cell of that pay period, if same person has two more sick days in other months of the same year formula will have to add 1 sick day to holiday cell instead 2 since we are allowed only 3 paid sick days. Goal is to have formula adding max of 3 sick days once person used up his/hers paid sick days it will stop adding them to holidays cell. I have feeling that formula will have to have some if; sum; max functions not quit sure since i am not that good in excel.
I tried to create it but it doesn't work for me. I have separate column that counts SUM of all sick days, might help to mention since I am not able to attach full work sheet. Rows in a sheet are different people.
One work book has12 sheets, 24 pay periods and SUM cells for sick days. All sick cells will have to work across work book. I am attaching just portion of work book since size of the sheet is to big to upload here. Here is example of just important section.
I am new to excel and just know how to use some of basic formulas.
So I created work book for bonus structure and I am stuck one formula. Work book has 12 sheets, one sheet for each month of the year and each sheet has two cells for first and second pay period of the month. To explain a bit more company pays 3 sick days in a year and they don't affect bonus. So lets say person has 1 sick day in first pay period of January and 1 sick day in second pay period of January. Formula will have to add them to holiday days in those periods. Lets say that same person has 1 more sick day in May, formula will add that sick day to holiday cell of the same pay period. Since person used his 3 payed days any other sick days will not be counted how ever we want to be able to keep adding them so we have total of sick days on the end of the year. Other example if person has 4 sick days in row formula will have to add 3 sick days to holiday cell and not count the 4th one or 5th one depending on how many days in a row did they have. . Other example: if person has 2 sick days in same pay period formula will add them to holiday cell of that pay period, if same person has two more sick days in other months of the same year formula will have to add 1 sick day to holiday cell instead 2 since we are allowed only 3 paid sick days. Goal is to have formula adding max of 3 sick days once person used up his/hers paid sick days it will stop adding them to holidays cell. I have feeling that formula will have to have some if; sum; max functions not quit sure since i am not that good in excel.
I tried to create it but it doesn't work for me. I have separate column that counts SUM of all sick days, might help to mention since I am not able to attach full work sheet. Rows in a sheet are different people.
One work book has12 sheets, 24 pay periods and SUM cells for sick days. All sick cells will have to work across work book. I am attaching just portion of work book since size of the sheet is to big to upload here. Here is example of just important section.
| Copy for forum.xlsx | ||||||||
|---|---|---|---|---|---|---|---|---|
| C | D | E | F | G | H | |||
| 6 | 1st Pay Period Paid Hours | 1st Pay Period Vacation Days/Traning Days | 1st Pay Period Sick Days | 2nd Pay Period Paid Hours | 2nd Pay Period Vacation Days/Traning Days | 2nd Pay Period sick Days | ||
| 7 | 0.00 | 0 | 0 | 0.00 | 0 | 0 | ||
| 8 | 0.00 | 0 | 0 | 0.00 | 0 | 0 | ||
| 9 | 0.00 | 0 | 0 | 0.00 | 0 | 0 | ||
| 10 | 0.00 | 0 | 0 | 0.00 | 0 | 0 | ||
| 11 | 0.00 | 0 | 0 | 0.00 | 0 | 0 | ||
| 12 | 0.00 | 0 | 0 | 0.00 | 0 | 0 | ||
| 13 | 0.00 | 0 | 0 | 0.00 | 0 | 0 | ||
| 14 | 0.00 | 0 | 0 | 0.00 | 0 | 0 | ||
| 15 | 0.00 | 0 | 0 | 0.00 | 0 | 0 | ||
Jan | ||||||||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| E7 | Expression | =$U7>3 | text | NO |
| H7 | Expression | =$U7>3 | text | NO |
| E8 | Expression | =$U8>3 | text | NO |
| F7:F15 | Expression | =$F7<=0 | text | NO |
| C18,C7:C16,C20:C30 | Expression | =$C7<=0 | text | NO |
| C7:C18 | Expression | =$C7>=(($D$3-$D7)*8)*1.1 | text | NO |
| F7:F15 | Expression | =$F7>=(($D$4-$G7)*8)*1.1 | text | NO |
