megera716
Board Regular
- Joined
- Jan 3, 2013
- Messages
- 140
- Office Version
- 365
- Platform
- Windows
Not even sure how to word this concisely.
We pay our salespeople commission on the 20th of the month after a customer pays in full. I have 5 columns of payment dates for each customer and another column called Commission Month containing this formula:
=IF(AR2=0,MONTH(MAX(AC2,AE2,AG2,AI2,AK2))+1,"")
So if the customer's balance due is $0, take the latest month that they paid and then add 1 so it returns the next month and that's when I should pay the commission. Then I pivot that data filtered on Commission Month to put it all together in a pretty little format. This worked great halfway through the year last year when I came up with the formula but now I'm getting multiple years of data and need to make sure it keeps working great so I want it to return a full date in MM/DD/YY format.
If the latest date in the range is 1/1/22, I want it to return 2/20/22. If it's 1/31/22, it should still return 2/20/22. If the latest date is 2/7/22, it should return 3/20/22. And so on. Add 1 month, day is always the 20th.
I just have no clue how to word/write the formula for that. And it's really hard to Google it when you can't think of how to explain it in less than 4 paragraphs ?
We pay our salespeople commission on the 20th of the month after a customer pays in full. I have 5 columns of payment dates for each customer and another column called Commission Month containing this formula:
=IF(AR2=0,MONTH(MAX(AC2,AE2,AG2,AI2,AK2))+1,"")
So if the customer's balance due is $0, take the latest month that they paid and then add 1 so it returns the next month and that's when I should pay the commission. Then I pivot that data filtered on Commission Month to put it all together in a pretty little format. This worked great halfway through the year last year when I came up with the formula but now I'm getting multiple years of data and need to make sure it keeps working great so I want it to return a full date in MM/DD/YY format.
If the latest date in the range is 1/1/22, I want it to return 2/20/22. If it's 1/31/22, it should still return 2/20/22. If the latest date is 2/7/22, it should return 3/20/22. And so on. Add 1 month, day is always the 20th.
I just have no clue how to word/write the formula for that. And it's really hard to Google it when you can't think of how to explain it in less than 4 paragraphs ?