Sick Accrual with Multiple Rules

[mrshifty]

I am trying to streamline the sick time accruals via excel (current HRIS does not have this feature, crazy I know). I can get the basic down with accruals, max, used, etc. Here are the screenshot of the various rules. My initial idea was to have a separate sheet which houses all the rules and the location sheet would a single formula derived from the rule sheet. I can't seem to get a single formula to work with all the rules and find myself making different formulas for each location, especially those with more generous accruals

A3 - location name
F4 - rules for reference that is changed via A3, pulls from the Mapping sheet
Column D - helper column, main formula (hours worked/30)
Column F - carry over hours from previous year
Column G - total hours worked (main sheet uses a vlookup)
Column H - accrued using the rules from the Mapping sheet
Column I - used hours
Column J - available hours (carry over + accrued minus used) and should account for limits per location
Mapping Sheet - rules per location

Is it possible to have a single formula for all locations or do I have to make different formulas for different accruals?

[Ochimus]

mrshifty

Try the attached approach?
A3 is a DropList linked to your choices in Mapping Col A.
Row 6 lists the various "Caps" automatically for whichever site you select, so your "actual" numbers can deduct from that, rather than multiple "ifs".
Also has the Hours as actual clock times, formatted to allow "open ended" hours rather than only twenty four.

Hope it might help?

Ochimus