Listed below is a dummy spreadsheet. I am trying how to get column E not to exceed 24 hours and row 27 column C (total accrued sick time) not to exceed 48.
The formula is regular hours/30=accrued sick time.
You cannot accrue more than 48 and cannot use more than 24 available hours per calendar year. Any ideas?
<colgroup><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>
The formula is regular hours/30=accrued sick time.
You cannot accrue more than 48 and cannot use more than 24 available hours per calendar year. Any ideas?
Pay Period | Pay Period End | Regular Hours | Sick Time Used | Available Hours |
07/01/17 | 07/16/17 | 85.25 | 2.84 | |
07/16/17 | 07/31/17 | 94.50 | 5.99 | |
08/01/17 | 08/16/17 | 71.00 | 8.36 | |
08/16/17 | 08/31/17 | 88.00 | 11.29 | |
09/01/17 | 09/15/17 | 80.00 | 8.00 | 5.96 |
09/16/17 | 09/30/17 | 80.00 | 8.63 | |
10/01/17 | 10/15/17 | 88.00 | 11.56 | |
10/16/17 | 10/31/17 | 72.00 | 13.96 | |
11/01/17 | 11/15/17 | 78.75 | 16.58 | |
11/16/17 | 11/30/17 | 77.00 | 16.00 | 3.15 |
12/01/17 | 12/15/17 | 72.00 | 5.55 | |
12/16/17 | 12/31/17 | 96.00 | 8.75 | |
01/01/18 | 01/15/18 | 80.00 | 11.42 | |
01/16/18 | 01/31/18 | 56.00 | 13.28 | |
02/01/18 | 02/15/18 | 88.00 | 16.22 | |
02/16/18 | 02/28/18 | 92.75 | 19.31 | |
03/01/18 | 03/15/18 | 80.00 | 21.98 | |
03/16/18 | 03/31/18 | 76.50 | 24.53 | |
04/01/18 | 04/15/18 | 88.00 | 27.46 | |
04/16/18 | 04/30/18 | 88.00 | 30.39 | |
05/01/18 | 05/15/18 | 88.00 | 33.33 | |
05/16/18 | 05/31/18 | 88.00 | 36.26 | |
06/01/18 | 06/15/18 | 72.00 | 38.66 | |
06/16/18 | 06/30/18 | 88.00 | 41.59 | |
Total hours | 1967.75 | 24.00 | ||
Total Accrued Sick Time | 41.59 |
<colgroup><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>