Accrued Sick Pay

[MzNikki]

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?

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>

 

[Beyond_avarice]

Place this in E3:

=IF((E2+(C3/30)-D3)>48,48,(E2+(C3/30)-D3))

Keep E2 as hours/30 (C2/30).

 

[MzNikki]

Thank you. The only problem I am having is that once the hours are used, the 24 number must decrease by that amount if that makes any sense. 24 are available and only 24 can be used. I'm not even sure an equation can accommodate what I am trying to do. I appreciate your speedy response.

Place this in E3:

=IF((E2+(C3/30)-D3)>48,48,(E2+(C3/30)-D3))

Keep E2 as hours/30 (C2/30).

 

[Scott T]

To stop from accruing more then 48 hours put this in E2 and copy down.

=MIN(ROUND((SUM($C$2:C2)/30)-SUM($D$2:D2),2),48)

If more then 24 hours total is imputed into sick time used then this code triggers a message box and clears the cell that was entered. Change the range to match your data

Right click on the sheet tab and select view code and past the code below in the VBA editor.
The file will need to be saved as a macro enabled file type like .XLSM and macros must be enabled for this to stop more than 24 hours being used.

Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, Range("[COLOR=#ff0000]D2:D25[/COLOR]")) Is Nothing Then
    usedsick = Application.Sum(Range("[COLOR=#ff0000]D2:D25[/COLOR]"))
    If usedsick > 24 Then
        MsgBox ("Error only 24 hours of sick can be used")
        Target.ClearContents
    End If


End If

 

[Beyond_avarice]

So how about "=24-Sum(D3:D"where ever this ends")"?

 

[MzNikki]

Listed below is a dummy spreadsheet. I am trying to get column E not to exceed 24 hours but still subtract column D until the number in column E is zero.

Row 27 column C (total accrued sick time) cannot exceed 48 hours.

The formula is regular hours/30=accrued sick time, minus sick time used. Available hours can not exceed 24, but must be reduced to zero if sick time is used.

In cell C27, you cannot accrue more than 48. And yes this is complicated and hard to explain. 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

<tbody>
</tbody>

Thank you in advance.

 

[TonyUK72]

FORMULA E2:

=C2/30

FORMULA E3: "Copy this formula down"

=IF(((C3/30)+E2)-D3>24,24,((C3/30)+E2)-D3)

FORMULA C27:

=IF((C26/30)-D26>48,48,(C26/30)-D26)

Hope this helps

 

[SyedUsman]

Formula for cell E2

=MIN(IFERROR(C2/30+E1,C2/30),24)-D2

 

[MzNikki]

Thank you all for your responses. I still have to attempt a few of these as I am not very Excel savy, but I do want you to know I appreciate the help. I will keep you updated.