• 461K Members
• 4,978 Online
• 559K Conversations
SOLVED

New Contributor

If, then formula

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.

3 Replies
Solution

Re: If, then formula

@_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))`

Re: If, then formula

This works perfectly! Thank you.

Re: If, then formula

@_janelllllle , you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies