IF/AND or IF/OR formula help...please

[semperduro]

I need help with what I think is a complex formula. I’m sure it’s not and I’m just overthinking it.

My job uses longevity pay as an incentive and it’s factored into your hourly rate, but there are parameters involved.

Prior to Jan 1, 2023, you were paid $5.85 per month until you reached 10 yrs (120 months).

At the 10 yr (120 month) mark you received $10.00 per month in longevity pay.

Effective Jan 1, 2023, you do not receive longevity pay until you reach your 10-yr mark. Meaning, hire date to year 10 you receive ZERO longevity pay.

How would I write that IF/OR, or IF/AND formula?

Currently my formula is:
=DATEDIF ($B$1, TODAY(), “M”)*5.85
(Cell $B$1is where you enter your hire date in the MM/DD/YYYY format.)

I am using the Office 365 package if that helps. As I know there are new formulas out with this package that simplify things. However, I still can't crack this nut.

Any assistance is greatly appreciated.

Jon

 

[Joe4]

So we are sure that we clearly understand the logic you want to use, can you post some examples and expected results, trying to hit all the different scenarios?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Joe4]

This is my understanding, to calculate the rate:

The formula I used for cell C1 is:

=IF(DATEDIF(B1,TODAY(),"M")>=120,10,IF(B1<DATE(2023,1,1),5.85,0))