PTO Tracking Rollover from Previous Year

[C Barrett]

Hey guys! I'm needing a little bit of assistance with a formula that calculates how many PTO days were available to an employee in their previous year of employment, to rollover a maximum of 1 day into the next year of employment. So if you get 11 days available immediately each anniversary, and last year you only used 10 days, this year you would have 12 available.

Our basic PTO policy is this:
1 Day available after first 6 months of employment
12 Days available after first anniversary, renewed annually
17 Days available after 5th anniversary, renewed annually
1 Day can roll over from previous period if unused

I currently have a formula that calculates how many days were available in their last PTO period:
(I3 = Hire Date, F1 = Today's Date, O3 = Rollover from last period)

=DATEDIF($I3,$F$1,"m")-12>=6,1,0)+IF(DATEDIF($I3,$F$1,"y")-1>=1,11,0)+IF(DATEDIF($I3,$F$1,"y")-1>=5,5,0)+$O3

And this formula is subtracting the number of days used in that last period from the total available in the last period:
(I3 = Hire Date, F1 = Today's Date, O3 = Rollover from last period)
(SUMIFS(Quantity Used if employee name matches, and date used is more recent than two years before their next anniversary date, but older than their last anniversary date)

=IF(DATEDIF($I3,$F$1,"m")-12>=6,1,0)+IF(DATEDIF($I3,$F$1,"y")-1>=1,11,0)+IF(DATEDIF($I3,$F$1,"y")-1>=5,5,0)+$O3-SUMIFS($C:$C,$A:$A,$H3,$B:$B,">="&($J3-730),$B:$B,"<"&($J3-365))

But my issue is calculating the rollover (O3). They can only have a maximum of 1 day rollover no matter their tenure. So I need a formula that outputs a value no more than 1, and no less than 0 after calculating the available days in the previous period (including rollover from the previous previous period.......) minus days used in the previous period.

This is my current formula to calculate how much PTO is rolling over from the previous period in the current period:

=IF(IF(IF(DATEDIF($I6,$F$1,"m")>6,1,0)+IF(DATEDIF($I6,$F$1,"y")>1,11,0)+IF(DATEDIF($I6,$F$1,"y")>=5,5,0)+$O6-SUMIFS($C:$C,$A:$A,$H6,$B:$B,">="&($J6-730),$B:$B,"<"&($J6-365))>=1,1,IF(DATEDIF($I6,$F$1,"m")>6,1,0)+IF(DATEDIF($I6,$F$1,"y")>1,11,0)+IF(DATEDIF($I6,$F$1,"y")>=5,5,0)+$O6-SUMIFS($C:$C,$A:$A,$H6,$B:$B,">="&($J6-730),$B:$B,"<"&($J6-365)))<=0,0,IF(IF(DATEDIF($I6,$F$1,"m")>6,1,0)+IF(DATEDIF($I6,$F$1,"y")>1,11,0)+IF(DATEDIF($I6,$F$1,"y")>=5,5,0)+$O6-SUMIFS($C:$C,$A:$A,$H6,$B:$B,">="&($J6-730),$B:$B,"<"&($J6-365))>=1,1,IF(DATEDIF($I6,$F$1,"m")>6,1,0)+IF(DATEDIF($I6,$F$1,"y")>1,11,0)+IF(DATEDIF($I6,$F$1,"y")>=5,5,0)+$O6-SUMIFS($C:$C,$A:$A,$H6,$B:$B,">="&($J6-730),$B:$B,"<"&($J6-365))))

IS THERE A SIMPLER WAY TO WRITE THIS? BECAUSE I'M PULLING MY HAIR OUT AND GETTING OUTPUTS OF MORE THAN 1.

Please and thank you, any help is so very much appreciated! Also, if you have any questions (which I'm expecting there will be!) just ask

 

[Bdra]

On the principle of K.I.S.S., how about adding a cell next to O3 with the following formula:

=IF(O3>0,1,0)

That will be your effective rollover value (the value of this cell will always be either 0 or 1). Then rewrite the dependent formula to pull from this cell instead of O3.