Want to help achieving result

[James Clear]

Hi team,

I want to start counting payments basis month

If suppose my month is given Aug'23 then paym1 will be placed in Aug'23 and then Sep'23 , Oct'23 so on

If suppose month is Jun'23 then Payment1 will be placed in Jun'23

Pls help me ASAP

 

[James Clear]

Hi team,

I want to start counting payments basis month

If suppose my month is given Aug'23 then paym1 will be placed in Aug'23 and then Sep'23 , Oct'23 so on

If suppose month is Jun'23 then Payment1 will be placed in Jun'23

Pls help me ASAP



View attachment 101557

Pls help team

 

[James Clear]

Appreciate your help

 

[Alex Blakenburg]

You seem to be covering off a lot of versions in what you are using. Also you Month headings are all over the place and the sample output seems to assume they are in order which they are not.

Someone else can probably give you a more complete solution especially if you can use an MS 365 solution in the meantime see if this helps.
The Let makes the formula smaller but this can be done without using the Let.

20231107 Payment Schedule based on Min Max James Clear.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Input									Output
2	Paym1	Paym2	Paym3	Paym4	Paym5	Paym6	Paym7	Paym8	Month	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24
3	62	44	34	49	42	86	69	1	Jul-23							62	44	34	49	42	86	69	1
4	1	84	3	84	68	44	71	72	Sep-23									1	84	3	84	68	44	71	72
5	15	11	29	97	91	11	72	15	Aug-23								15	11	29	97	91	11	72	15
6	29	38	12	44	22	65	31	34	Jun-23						29	38	12	44	22	65	31	34
Sheet1

Cell Formulas
Range	Formula
J3:Z6	J3	=LET(mthNo,IFERROR(DATEDIF($I3,J$2,"m"),0), rPaymHdg,$A$2:$H$2, rPaym,$A3:$H3, IF(OR(mthNo=0,mthNo>COUNTA(rPaymHdg)),"",INDEX(rPaym,0,mthNo)))

 

[James Clear]

62 should have been in Jul-23 and not in Aug-23

 

[James Clear]

You seem to be covering off a lot of versions in what you are using. Also you Month headings are all over the place and the sample output seems to assume they are in order which they are not.

Someone else can probably give you a more complete solution especially if you can use an MS 365 solution in the meantime see if this helps.
The Let makes the formula smaller but this can be done without using the Let.

20231107 Payment Schedule based on Min Max James Clear.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Input									Output
2	Paym1	Paym2	Paym3	Paym4	Paym5	Paym6	Paym7	Paym8	Month	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24
3	62	44	34	49	42	86	69	1	Jul-23							62	44	34	49	42	86	69	1
4	1	84	3	84	68	44	71	72	Sep-23									1	84	3	84	68	44	71	72
5	15	11	29	97	91	11	72	15	Aug-23								15	11	29	97	91	11	72	15
6	29	38	12	44	22	65	31	34	Jun-23						29	38	12	44	22	65	31	34
Sheet1

Cell Formulas
Range	Formula
J3:Z6	J3	=LET(mthNo,IFERROR(DATEDIF($I3,J$2,"m"),0), rPaymHdg,$A$2:$H$2, rPaym,$A3:$H3, IF(OR(mthNo=0,mthNo>COUNTA(rPaymHdg)),"",INDEX(rPaym,0,mthNo)))

Yes this helped me… appreciate, if you could explain me the last piece of this puzzle.

OR(mthNo=0,mthNo>COUNTA(rPaymHdg)),"",INDEX(rPaym,0,mthNo)))[/XD][/XR][/RANGE]

 

[Alex Blakenburg]

62 should have been in Jul-23 and not in Aug-23

Just fix this I just added the "+1" in the formula calculating the MthNo

=LET(mthNo,IFERROR(DATEDIF($I3,J$2,"m")+1,0),
rPaymHdg,$A$2:$H$2,
rPaym,$A3:$H3,
IF(OR(mthNo=0,mthNo>COUNTA(rPaymHdg)),"",INDEX(rPaym,0,mthNo)))

if you could explain me the last piece of this puzzle.

IF(OR(mthNo=0,mthNo>COUNTA(rPaymHdg)),"",INDEX(rPaym,0,mthNo)))

When the date in the heading matches the date in the Month column I then DateDif will = 0 months. As per the above this should be the first figure we want so I have added +1.
If the date is before the heading month then date dif will calculate a negative value and error out. The IfError will convert that error to returning a zero.
This is one of the conditions in the OR statement which will then return "" if the month is earlier than the month in the heading.
The other part of the OR statement will check / count how many Paym columns we need to cater for and once we exceed that number of months it alo needs to return a "".
Index is returning the respective Paym column from 1 to the number of Paym columns (currently 8) for the current row.
Let me know if I have only added to your confusion or whether the explanation works for you