Accrued Sick Pay

M

MzNikki

New Member
Joined
Aug 23, 2017
Messages
4
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?

[TABLE="width: 666"]
<colgroup><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD]Pay Period[/TD]
[TD]Pay Period End[/TD]
[TD]Regular Hours[/TD]
[TD]Sick Time Used[/TD]
[TD]Available Hours[/TD]
[/TR]
[TR]
[TD]07/01/17[/TD]
[TD]07/16/17[/TD]
[TD]85.25[/TD]
[TD][/TD]
[TD]2.84[/TD]
[/TR]
[TR]
[TD]07/16/17[/TD]
[TD]07/31/17[/TD]
[TD]94.50[/TD]
[TD][/TD]
[TD]5.99[/TD]
[/TR]
[TR]
[TD]08/01/17[/TD]
[TD]08/16/17[/TD]
[TD]71.00[/TD]
[TD][/TD]
[TD]8.36[/TD]
[/TR]
[TR]
[TD]08/16/17[/TD]
[TD]08/31/17[/TD]
[TD]88.00[/TD]
[TD][/TD]
[TD]11.29[/TD]
[/TR]
[TR]
[TD]09/01/17[/TD]
[TD]09/15/17[/TD]
[TD]80.00[/TD]
[TD]8.00[/TD]
[TD]5.96[/TD]
[/TR]
[TR]
[TD]09/16/17[/TD]
[TD]09/30/17[/TD]
[TD]80.00[/TD]
[TD][/TD]
[TD]8.63[/TD]
[/TR]
[TR]
[TD]10/01/17[/TD]
[TD]10/15/17[/TD]
[TD]88.00[/TD]
[TD][/TD]
[TD]11.56[/TD]
[/TR]
[TR]
[TD]10/16/17[/TD]
[TD]10/31/17[/TD]
[TD]72.00[/TD]
[TD][/TD]
[TD]13.96[/TD]
[/TR]
[TR]
[TD]11/01/17[/TD]
[TD]11/15/17[/TD]
[TD]78.75[/TD]
[TD][/TD]
[TD]16.58[/TD]
[/TR]
[TR]
[TD]11/16/17[/TD]
[TD]11/30/17[/TD]
[TD]77.00[/TD]
[TD]16.00[/TD]
[TD]3.15[/TD]
[/TR]
[TR]
[TD]12/01/17[/TD]
[TD]12/15/17[/TD]
[TD]72.00[/TD]
[TD][/TD]
[TD]5.55[/TD]
[/TR]
[TR]
[TD]12/16/17[/TD]
[TD]12/31/17[/TD]
[TD]96.00[/TD]
[TD][/TD]
[TD]8.75[/TD]
[/TR]
[TR]
[TD]01/01/18[/TD]
[TD]01/15/18[/TD]
[TD]80.00[/TD]
[TD][/TD]
[TD]11.42[/TD]
[/TR]
[TR]
[TD]01/16/18[/TD]
[TD]01/31/18[/TD]
[TD]56.00[/TD]
[TD][/TD]
[TD]13.28[/TD]
[/TR]
[TR]
[TD]02/01/18[/TD]
[TD]02/15/18[/TD]
[TD]88.00[/TD]
[TD][/TD]
[TD]16.22[/TD]
[/TR]
[TR]
[TD]02/16/18[/TD]
[TD]02/28/18[/TD]
[TD]92.75[/TD]
[TD][/TD]
[TD]19.31[/TD]
[/TR]
[TR]
[TD]03/01/18[/TD]
[TD]03/15/18[/TD]
[TD]80.00[/TD]
[TD][/TD]
[TD]21.98[/TD]
[/TR]
[TR]
[TD]03/16/18[/TD]
[TD]03/31/18[/TD]
[TD]76.50[/TD]
[TD][/TD]
[TD]24.53[/TD]
[/TR]
[TR]
[TD]04/01/18[/TD]
[TD]04/15/18[/TD]
[TD]88.00[/TD]
[TD][/TD]
[TD]27.46[/TD]
[/TR]
[TR]
[TD]04/16/18[/TD]
[TD]04/30/18[/TD]
[TD]88.00[/TD]
[TD][/TD]
[TD]30.39[/TD]
[/TR]
[TR]
[TD]05/01/18[/TD]
[TD]05/15/18[/TD]
[TD]88.00[/TD]
[TD][/TD]
[TD]33.33[/TD]
[/TR]
[TR]
[TD]05/16/18[/TD]
[TD]05/31/18[/TD]
[TD]88.00[/TD]
[TD][/TD]
[TD]36.26[/TD]
[/TR]
[TR]
[TD]06/01/18[/TD]
[TD]06/15/18[/TD]
[TD]72.00[/TD]
[TD][/TD]
[TD]38.66[/TD]
[/TR]
[TR]
[TD]06/16/18[/TD]
[TD]06/30/18[/TD]
[TD]88.00[/TD]
[TD][/TD]
[TD]41.59[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total hours[/TD]
[TD]1967.75[/TD]
[TD]24.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total Accrued Sick Time[/TD]
[TD]41.59[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Place this in E3:

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

Keep E2 as hours/30 (C2/30).
 
Last edited:
Upvote 0
:) 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.

Beyond_avarice said:
Place this in E3:

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

Keep E2 as hours/30 (C2/30).
Click to expand...
 
Upvote 0
To stop from accruing more then 48 hours put this in E2 and copy down.
Code: 
=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.


Code: 
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
 
Upvote 0
MzNikki said:
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?

[TABLE="width: 666"]
<tbody>[TR]
[TD]Pay Period
[/TD]
[TD]Pay Period End
[/TD]
[TD]Regular Hours
[/TD]
[TD]Sick Time Used
[/TD]
[TD]Available Hours
[/TD]
[/TR]
[TR]
[TD]07/01/17
[/TD]
[TD]07/16/17
[/TD]
[TD]85.25
[/TD]
[TD][/TD]
[TD]2.84
[/TD]
[/TR]
[TR]
[TD]07/16/17
[/TD]
[TD]07/31/17
[/TD]
[TD]94.50
[/TD]
[TD][/TD]
[TD]5.99
[/TD]
[/TR]
[TR]
[TD]08/01/17
[/TD]
[TD]08/16/17
[/TD]
[TD]71.00
[/TD]
[TD][/TD]
[TD]8.36
[/TD]
[/TR]
[TR]
[TD]08/16/17
[/TD]
[TD]08/31/17
[/TD]
[TD]88.00
[/TD]
[TD][/TD]
[TD]11.29
[/TD]
[/TR]
[TR]
[TD]09/01/17
[/TD]
[TD]09/15/17
[/TD]
[TD]80.00
[/TD]
[TD]8.00
[/TD]
[TD]5.96
[/TD]
[/TR]
[TR]
[TD]09/16/17
[/TD]
[TD]09/30/17
[/TD]
[TD]80.00
[/TD]
[TD][/TD]
[TD]8.63
[/TD]
[/TR]
[TR]
[TD]10/01/17
[/TD]
[TD]10/15/17
[/TD]
[TD]88.00
[/TD]
[TD][/TD]
[TD]11.56
[/TD]
[/TR]
[TR]
[TD]10/16/17
[/TD]
[TD]10/31/17
[/TD]
[TD]72.00
[/TD]
[TD][/TD]
[TD]13.96
[/TD]
[/TR]
[TR]
[TD]11/01/17
[/TD]
[TD]11/15/17
[/TD]
[TD]78.75
[/TD]
[TD][/TD]
[TD]16.58
[/TD]
[/TR]
[TR]
[TD]11/16/17
[/TD]
[TD]11/30/17
[/TD]
[TD]77.00
[/TD]
[TD]16.00
[/TD]
[TD]3.15
[/TD]
[/TR]
[TR]
[TD]12/01/17
[/TD]
[TD]12/15/17
[/TD]
[TD]72.00
[/TD]
[TD][/TD]
[TD]5.55
[/TD]
[/TR]
[TR]
[TD]12/16/17
[/TD]
[TD]12/31/17
[/TD]
[TD]96.00
[/TD]
[TD][/TD]
[TD]8.75
[/TD]
[/TR]
[TR]
[TD]01/01/18
[/TD]
[TD]01/15/18
[/TD]
[TD]80.00
[/TD]
[TD][/TD]
[TD]11.42
[/TD]
[/TR]
[TR]
[TD]01/16/18
[/TD]
[TD]01/31/18
[/TD]
[TD]56.00
[/TD]
[TD][/TD]
[TD]13.28
[/TD]
[/TR]
[TR]
[TD]02/01/18
[/TD]
[TD]02/15/18
[/TD]
[TD]88.00
[/TD]
[TD][/TD]
[TD]16.22
[/TD]
[/TR]
[TR]
[TD]02/16/18
[/TD]
[TD]02/28/18
[/TD]
[TD]92.75
[/TD]
[TD][/TD]
[TD]19.31
[/TD]
[/TR]
[TR]
[TD]03/01/18
[/TD]
[TD]03/15/18
[/TD]
[TD]80.00
[/TD]
[TD][/TD]
[TD]21.98
[/TD]
[/TR]
[TR]
[TD]03/16/18
[/TD]
[TD]03/31/18
[/TD]
[TD]76.50
[/TD]
[TD][/TD]
[TD]24.53
[/TD]
[/TR]
[TR]
[TD]04/01/18
[/TD]
[TD]04/15/18
[/TD]
[TD]88.00
[/TD]
[TD][/TD]
[TD]27.46
[/TD]
[/TR]
[TR]
[TD]04/16/18
[/TD]
[TD]04/30/18
[/TD]
[TD]88.00
[/TD]
[TD][/TD]
[TD]30.39
[/TD]
[/TR]
[TR]
[TD]05/01/18
[/TD]
[TD]05/15/18
[/TD]
[TD]88.00
[/TD]
[TD][/TD]
[TD]33.33
[/TD]
[/TR]
[TR]
[TD]05/16/18
[/TD]
[TD]05/31/18
[/TD]
[TD]88.00
[/TD]
[TD][/TD]
[TD]36.26
[/TD]
[/TR]
[TR]
[TD]06/01/18
[/TD]
[TD]06/15/18
[/TD]
[TD]72.00
[/TD]
[TD][/TD]
[TD]38.66
[/TD]
[/TR]
[TR]
[TD]06/16/18
[/TD]
[TD]06/30/18
[/TD]
[TD]88.00
[/TD]
[TD][/TD]
[TD]41.59
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total hours
[/TD]
[TD]1967.75
[/TD]
[TD]24.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total Accrued Sick Time
[/TD]
[TD]41.59
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance.
Click to expand...
 
Upvote 0
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
 
Upvote 0
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. :biggrin:
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Count entry by time
Replies
3
Views
155
admvlad
A
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
228
Raj1979
R
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
235
dshtesy
D
S
  • Question Question
Two way look up with mutiple duplicate criteria with mutiple return
Replies
3
Views
170
sanrama
S
B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
54
Bernie Galve
B

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top