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

[DawnDuong]

Hi @Carlosast 

actually the solution is something similar to Excel

1) You create one column (“SimpleAddMonth”) using the formula datetimeadd([field], 1 ‘month’

2) You create another column (“EndOfNextMonth”) to calculate the last day of the next month. This is done by a combination of datetimetrim( datetimeadd ( datetimetrim ([field], ‘firstofmonth’), 1, ‘month’), ‘lastofmonth’)

3) then you add a formula tool to create the “Result” field

If isnull([SimpleAddMonth])

then [EndOfNextMonth]

else [SimpleAddMonth]

endif

the logic flow is similar to what you would do if you implement this in excel

cheers

[danilang]

Hi @Carlosast 

Here's a workflow that will perform an after the fact correction on your dates to get the correct date in all cases  Get the day number from your start date and after your Generate Rows, apply this formula to your dates

if datetimeday([date])<[DayNumber] and DateTimeDay(datetimetrim([Date],"lastofmonth"))>=[DayNumber] then
	left([Date],8)+padleft(tostring([DayNumber]),2,"0")
elseif daynumber=31 then
	todate(datetimetrim([Date],"lastofmonth"))
else
	[date]
endif

This first clause handles the cases 29 and 30 and the second is special case for having an end date of 31.

Dan