May 06 2019 08:41 AM - edited May 06 2019 08:59 AM
I need a formula to figure out how much vacation time I'll have. From 1-36 months of service, I get 11 hours per month. Then, from 37-120 months of service, I'll get 14 hours per month. From 121-180 months of service, I'll get 16 hours per month. How can I combine all of these? Say I have 46 months of service, I'll need to show that the first 36 months are at a rate of 11 hours per month and that every month after that (from month 37 to 46) is at a rate of 14 hours per month. I would like to fill out the "annual leave earned" cell based on the "service months to date" and "annual leave accrual rates" table in my worksheet.
May 06 2019 09:37 AM
Solution@_janelllllle , you may add helper range like this
The formula could be
=SUMPRODUCT((B9-$B$3:$B$7)*$C$3:$C$7*((B9-$B$3:$B$7)>0))
May 06 2019 09:37 AM
Solution@_janelllllle , you may add helper range like this
The formula could be
=SUMPRODUCT((B9-$B$3:$B$7)*$C$3:$C$7*((B9-$B$3:$B$7)>0))