I would like to calculate the end date for future plan. Please see the excel table, I noted the desired plan. Note: Start date is known, I have to calculate End date 1,2,3 and 4 Thank you in advance

[Venkata Siva Komma]

[Astrid Blee]

Hi!

It should be fairly easy to add a calculated column with a specific interval generated by the repetition/frequency.

Assuming frequency = number of months between end dates, i.e. a frequency of 1 month, then End Date 1 = Start Date + 1 month. This can be done using the DateAdd function:

e.g. DateAdd('month', [Frequency], [start Date]).

The remaining End Dates can be calculated by doing the same, but adding an If Statement around the above, checking if the number of repetitions has been satisfied - if yes, it yields NULL, if no then more repetitions are added:

e.g. If([Repetitions]>4,DateAdd('month',[Frequency],[start Date]),NULL)

I'm just working out if there's a clever way to set the If clause value to be calculated from the number of dates already calculated, will get that worked out this afternoon!

[Venkata Siva Komma]

Hi

Thank you for suggustions.

Currently we are using the same if function like you (If([Repetitions]>2,DateAdd('month',[Frequency],[start Date1]),NULL)

 ).

But We need something can calculate automatically from the last end date and continues, if any repetitions change in customer table.

I hope you will work it out. Thank you for your time.

Thank you

[Astrid Blee]

Hi Venkata,

A slightly ugly way of doing it is as follows:

• Concatenate all the End Date Values and calculate the length of that value
• Then if that is less than the number of repetitions * the length of one date, add in the new date
• If not, add in a null value

For me, this looks like:

If(Len(Concatenate([start Date],[End Date 1],[End Date 2],[End Date 3]))<(Len(String([start Date])) * [Repetitions]),DateAdd('month',[Frequency],[start Date]),NULL)

Obviously this has the disadvantage that the concatenate function has to be done manually for each column, but other than that it is all automatic.