Bi-weekly payments between two dates

[FDosSantos]

I’m working on an app that takes two fields as user input:
Contract Date (start_date);
Number of Months (months);

I need it to calculate all the bi-weekly payments from the [start_date] during the number of [months]. For example, if the user enters July 28 2020 and 2 months, I need the app to calculate/populate the following dates: Aug 11 2020, Aug 25 2020, Sep 8 2020, Sep 22 2020. I understand I can add 14 days to [start_date] --> date([start_date]+14) to find the first payment date, but how do I go from there, since the number of months is variable?

Thank you for any tips and advice,
Fabio

[LeventK]

FDosSantos:

if the user enters July 28 2020 and 2 months, I need the app to calculate/populate the following dates: Aug 11 2020, Aug 25 2020, Sep 8 2020, Sep 22 2020

Where do you want the app to populate those dates?

[FDosSantos]

Hi Levent, thanks for your quick reply. Preferably in a virtual column (just to display for users, the result will not be stored anywhere).

[LeventK · Accepted solution]

Provided you have a limit for the [Number of Months] value the best shot might be creating a list of hard-coded days and use the TOP() expression. I know how ugly it looks like, but I couldn’t think of another solution actually because there is no looping expression that we can handle it right at the moment:

TOP(
	{[Date]+14 , [Date]+28 , [Date] + 42 , [Date] + 56 , [Date] + 70 , [Date] + 84 , [Date] + 98 , [Date] + 112 , [Date] + 126 ...},
	[Number of Months]*2
)

[FDosSantos]

I’ll give that a shot. The number of months is limited to 6 months now, so it wouldn’t be a huge effort. Thank you so much Levent!

[LeventK]

You’re very welcome @FDosSantos, but try it with below expression. I have tested and LIST() expression works better:

TOP(
	LIST([Date]+14 , [Date]+28 , [Date] + 42 , [Date] + 56 , [Date] + 70 , [Date] + 84 , [Date] + 98 , [Date] + 112 , [Date] + 126 , [Date] + 140 , [Date] + 154 , [Date] + 168),
	[Number of Months]*2
)