IF/AND or IF/OR formula

[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))

 

[semperduro]

This is my understanding, to calculate the rate:

View attachment 121269

The formula I used for cell C1 is:

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

That gives me the value of either 10.00, 5.85 or 0, but it doesn't multiple the value by the number of months, for some reason.

My original formula calculated the number of months between the hire date and "today()", then multiplied that by 5.85 to give you a total dollar amount of what you will get paid. At the time this was originally built, 5.85 was the only amount offered to all employees. The change of getting nothing until you reach 120 months, and being grandfathered in at 5.85 until you reach 120 months is a recent contract negotiation.

 

[Joe4]

That gives me the value of either 10.00, 5.85 or 0, but it doesn't multiple the value by the number of months, for some reason.

My original formula calculated the number of months between the hire date and "today()", then multiplied that by 5.85 to give you a total dollar amount of what you will get paid. At the time this was originally built, 5.85 was the only amount offered to all employees. The change of getting nothing until you reach 120 months, and being grandfathered in at 5.85 until you reach 120 months is a recent contract negotiation.

That is because my formula is just calculating the rate!
You already figured out how to calculate the months in your formula. So simply multiply your month calculation by my rate calculation, i.e.

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

 

[semperduro]

That is because my formula is just calculating the rate!
You already figured out how to calculate the months in your formula. So simply multiply your month calculation by my rate calculation, i.e.

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

Thank Sir. That did it.

 

[Joe4]

You are welcome.
Glad I was able to help!