Avatar

Level 10

Hi,

 

the end date calculation isn't that trivial, since some months have more days than others, so simply adding a fixed amount of days won't work in some circumstances.

Here's a FormCalc script that will do take care of this. 

 

if (HasValue(StartDate) eq 1) then
	var cPattern = "DD/MM/YYYY"
	var nDate = Date2Num(StartDate.formattedValue, cPattern)
	var nStartDate = nDate
	var nEndDate
	var nCountMonths = 18
	var nUltimo
	var nPrevMonth
	var nNextMonth
	
	; Get target month
	while (nCountMonths gt 0) do
	    nPrevMonth = Num2Date(nDate, "M")
	    nDate = Sum(nDate, 1)
	    nNextMonth = Num2Date(nDate, "M")
	    if (nPrevMonth ne nNextMonth) then
		    nCountMonths = Sum(nCountMonths, -1)
		endif
	endwhile
	
	; Get the length of the end month (ultimo)
	nUltimo = nDate
	while (Num2Date(Sum(nUltimo, 1), "M") eq Num2Date(nDate, "M")) do
	    nUltimo = Sum(nUltimo, 1)
	endwhile

	; Set end date
        if (Num2Date(nStartDate, "D") gt Num2Date(nUltimo, "D"))  then
	    ; Set ultimo as end date if end month has less days than start month
	    nEndDate = nUltimo
	else
	    ; Set previous day of start month as day of month in end date
	   	nEndDate = Sum(nDate, Num2Date(Sum(nStartDate, -1), "D"), -1)
	endif
	$ = Num2Date(nEndDate, cPattern)
endif

 

Hope this helps!