Generate dates forward from a date

[Carlosast]

Hello guys, let´s see if you can help me.

I am trying to generate the dates from two different fields (month to month), the field [FECHA] to the field [FECHA FINAL PRESTAMO].

The problem is that when i use the "Generate Rows" tool with the formula that you can see in the screenshot 1 i get the next months but from January to February it goes to the 28 day (which is right) but at the moment to generate march it does not go to the 30 of March Screenshot 2. This is from a work when i am trying to recalculate a loan amortization. 

• Screenshot 1

• Screenshot 2

[Luke_C]

Hi @Carlosast 

The datetimeadd doesn't work like that unfortunately.. try this:

datetimetrim(datetimeadd([FECHA],1,'month'),'lastofmonth')

[DawnDuong]

hi @Carlosast 

The datetimeAdd means that it just adds 1 "unit" (e.g. month, year etc...) and ignoring the rest of the unit. If you add 1 month to 30-Aug then the formula gives the 30th of the next month (which may or may not exist). This nuance is often missed.

So if your use case requires the end of month, the right way is to use the Datetrim function like @Luke_C  has pointed out.

Dawn.

[Carlosast]

Thank you @Luke_C but it does not work :( , i am getting this

And this is what i am looking for:

[Luke_C]

Hi @Carlosast 

Just to confirm, you want it to always be the 30th day (other than february), even if the month has 31 days?

[Carlosast]

Hi @DawnDuong, @Luke_C ,

Yes, what i am looking for is to create a loan amortization table. The field [FECHA] represents the first payment (2019-08-30), next payment has to be paid the 2019-09-30 and so on, always the 30 of the month. The problem is that when we have to pay in February there is no 30 day so we pay the 29 in 2020, but next month as the others have to be paid the 30.

Thanks for the help! :)

[Luke_C]

Hi @Carlosast 

Try this - added a formula after to subtract a day if it's the 31st. 

[Carlosast]

Hi @Luke_C 

It works! but unfortunately just for this example, in other loan with an start date of 2020-01-29 to 2025-03-01 you get wrong results.

[Luke_C]

Hi @Carlosast 

That's an important requirement! Don't have time to look much now - maybe you can take a stab at it using the solutions provided as a starting point. That's how you'll best learn.

Here's a great resource on date functions: DateTime Functions | Alteryx Help 

[Carlosast]

Yes @Luke_C .

Anyway, thank you very much for the help provided. :)