Jump to content

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

Recommended Posts


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!

Link to comment
Share on other sites


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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...