ADD MONTHS rule

[janhes]

Thought this might be useful.

I think this handles everything apart from leap years. This is for text output. Add TODATETIME for datetime format.

=if(right(FROMDATETIME(DateValue,"{CCYYMMDD}") ,2) = word("31,28,31,30,31,30,31,31,30,31,30,31",",",TEXTTONUMBER(mid(FROMDATETIME(DateValue,"{CCYYMMDD}"),5,2) )),
word("31,28,31,30,31,30,31,31,30,31,30,31",",",mod(
TEXTTONUMBER(mid(FROMDATETIME(DateValue,"{CCYYMMDD}"),5,2) )+ 
MonthsToAdd, 12)),
right(FROMDATETIME(DateValue,"{CCYYMMDD}") ,2))
+ "/" +
NUMBERTOTEXT(mod(
TEXTTONUMBER(mid(FROMDATETIME(DateValue,"{CCYYMMDD}"),5,2) )
+ 
MonthsToAdd, 12))
+ "/" +
NUMBERTOTEXT( 
TEXTTONUMBER(left(FROMDATETIME(DateValue,"{CCYYMMDD}"),4)) 
+int((MonthsToAdd + TEXTTONUMBER(mid(FROMDATETIME(DateValue,"{CCYYMMDD}"),5,2) )) / 12) 
)

 

[janhes]

OK it's got bugs in it.
Try this one.

=
//Test for end of month
if(FROMDATETIME(DateValue,"{DD}")  = word("31,28,31,30,31,30,31,31,30,31,30,31",",",TEXTTONUMBER(FROMDATETIME(DateValue,"{MM}") )) |
    FROMDATETIME(DateValue,"{DD}")  = word("31,29,31,30,31,30,31,31,30,31,30,31",",",TEXTTONUMBER(FROMDATETIME(DateValue,"{MM}") )) ,
//Day part
//If end of month get new month end of month
	word("31,28,31,30,31,30,31,31,30,31,30,31",",",
		if(mod(TEXTTONUMBER(FROMDATETIME(DateValue,"{MM}") )+ 
		MonthsToAdd + texttonumber(fromdatetime(DateValue,"{CCYY}")) * 12,12) = 0,
		12,
		mod(TEXTTONUMBER(FROMDATETIME(DateValue,"{MM}") )+ 
		MonthsToAdd  + texttonumber(fromdatetime(DateValue,"{CCYY}")) * 12, 12))),
	// otherwise use day
	FROMDATETIME(DateValue,"{DD}") )
+ "/" +
//Month part
fillleft(NUMBERTOTEXT(
	// If resulting month is December
	if(mod(TEXTTONUMBER(FROMDATETIME(DateValue,"{MM}") )
	+ MonthsToAdd + texttonumber(fromdatetime(DateValue,"{CCYY}")) * 12, 12) = 0,
	12,
	//otherwise
	mod(TEXTTONUMBER(FROMDATETIME(DateValue,"{MM}") )
	+ MonthsToAdd + texttonumber(fromdatetime(DateValue,"{CCYY}")) * 12, 12))),
"0",2)
+ "/" + 
//Year part
if(
	// If resulting month NOT December
	mod(TEXTTONUMBER(FROMDATETIME(DateValue,"{CCYY}")) *12 +
	MonthsToAdd + TEXTTONUMBER(FROMDATETIME(DateValue,"{MM}") ),12) > 0,
	NUMBERTOTEXT( 
			int((TEXTTONUMBER(FROMDATETIME(DateValue,"{CCYY}")) *12 +
			MonthsToAdd + TEXTTONUMBER(FROMDATETIME(DateValue,"{MM}") ))/12)) ,
	//otherwise reduce year by 1
	NUMBERTOTEXT( 
			int((TEXTTONUMBER(FROMDATETIME(DateValue,"{CCYY}")) *12 +
			MonthsToAdd + TEXTTONUMBER(FROMDATETIME(DateValue,"{MM}") )))/12 - 1)
)

 

[BocaBurger]

Now you get to add code for daylight savings changes when the map runs during the change over

Then figure in, how to do this when you are getting time sensitive database info and the DB is in another time zone / different daylight savings switch date.



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[janhes]

I don't think that's relevant. But it would be nice to have a function that did ADDMONTHS.