Return MM/DD/YY date from MAX date in range

[megera716]

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 ?

 

[jtakw]

Hi,

If I understand correctly, this should do what you want.
Change/adjust cell references accordingly:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		4/20/2022
2	0		1/1/2022		1/31/2022		2/7/2022		3/2/2022
Sheet981

Cell Formulas
Range	Formula
B1	B1	=IF(A2=0,EOMONTH(MAX(C2,E2,G2,I2,K2),0)+20,"")

 

[megera716]

Hi,

If I understand correctly, this should do what you want.
Change/adjust cell references accordingly:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		4/20/2022
2	0		1/1/2022		1/31/2022		2/7/2022		3/2/2022
Sheet981

Cell Formulas
Range	Formula
B1	B1	=IF(A2=0,EOMONTH(MAX(C2,E2,G2,I2,K2),0)+20,"")

Just gave it a shot, that is EXACTLY it. Thank you so much!!!

 

[jtakw]

You're welcome, thanks for the feedback.