specific function to add months in a date

chulett · Post by **chulett** » Thu May 18, 2006 7:12 am

I've asked similar questions over in the new post that was just started in the Server forum. Hopefully, they will come back here and answer them for you.

janhess · Post by **janhess** » Thu May 18, 2006 7:36 am

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

Code: Select all

=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) 
)

chulett · Post by **chulett** » Thu May 18, 2006 11:43 pm

Well... hopefully all your answers will help someone in the future. The original poster has moved over to the Server forum as they apparently posted here first by accident. Doh.

jgibby · Post by **jgibby** » Fri May 19, 2006 2:21 pm

Well, I finally finished a formula that will (according to my rigorous

testing standards) perform the function off adding or subtracting months to/from a date. (To subtract you would put a negative number in the MonthsToAdd object.) In addition to handling leap year, it will handle situations when the original day of the month doesn't exist in the target month (ie. 31st of Feb. is invalid). In that case it takes the last day of the month.

I don't profess to say this is the best way to go about it, but it does work. Unfortunately it is long, so take it for what it is worth.

Code: Select all

=EITHER(
	FROMDATETIME(
		TODATETIME(
			// Month
			FILLLEFT(
				NUMBERTOTEXT(
					MOD(
						TEXTTONUMBER(
							FROMDATETIME(DateValue,"{MM}")
						)
						+(IF(
							MonthsToAdd<0
							,-1
							,1
						)
						* IF(
								MOD(MonthsToAdd,12)=0
								,12
								,MOD(MonthsToAdd,12)
							)
						)
						+IF(MonthsToAdd<0,12)
						,12
					)
					+IF(
						MOD(
							TEXTTONUMBER(
								FROMDATETIME(DateValue,"{MM}")
							)
							+MonthsToAdd
							+IF(MonthsToAdd<0,12)
							,12
						)=0
						,12
						,0
					)
				)
				,"0"
				,2
			)
			+ "/" // Separator
			// Day of Month
			+ FROMDATETIME(DateValue,"{DD}")
			+ "/" // Separator
			// Year
			+ NUMBERTOTEXT(
				TEXTTONUMBER(
					FROMDATETIME(DateValue,"{CCYY}")
				)
				+ TRUNCATE(
					(
						TEXTTONUMBER(
							FROMDATETIME(DateValue,"{MM}")
						)
						+MonthsToAdd
						-12
						+IF(
							MonthsToAdd > 0
							,-1
							,0
						)
					)
					/12
				)
				+IF(
					MonthsToAdd > 0
					,1
					,0
				)
			)
			,"{MM/DD/CCYY}"
		)
		,"{MM/DD/CCYY}"
	)
	// Since the resulting date is not valid, we will increment
	// one month, set the day of the month to the 1st, and then
	// convert it to a date.  Then subtract one day and convert
	// it back to a date.  This will put us on the last valid 
	// day of the target month.
	,FROMDATETIME(
		NUMBERTODATE(
			DATETONUMBER(
				TODATETIME(
					// Month
					FILLLEFT(
						NUMBERTOTEXT(
							MOD(
								TEXTTONUMBER(
									FROMDATETIME(DateValue,"{MM}")
								)
								+(IF(
									MonthsToAdd<0
									,-1
									,1
								)
								* IF(
										MOD(MonthsToAdd,12)=0
										,12
										,MOD(MonthsToAdd,12)
									)
								)
								+IF(MonthsToAdd<0,12)
								+ 1 // Next Month Increment
								,12
							)
							+IF(
								MOD(
									TEXTTONUMBER(
										FROMDATETIME(DateValue,"{MM}")
									)
									+MonthsToAdd
									+IF(MonthsToAdd<0,12)
									+ 1 // Next Month Increment
									,12
								)=0
								,12
								,0
							)
						)
						,"0"
						,2
					)
					+ "/" // Separator
					// Day of Month
					+ "01" // First Day of Next Month 
					//+ FROMDATETIME(DateValue,"{DD}")
					+ "/" // Separator
					// Year
					+ NUMBERTOTEXT(
						TEXTTONUMBER(
							FROMDATETIME(DateValue,"{CCYY}")
						)
						+ TRUNCATE(
							(
								TEXTTONUMBER(
									FROMDATETIME(DateValue,"{MM}")
								)
								+MonthsToAdd
								+ 1 // Next Month Increment
								-12
								+IF(
									MonthsToAdd > 0
									,-1
									,0
								)
							)
							/12
						)
						+IF(
							MonthsToAdd > 0
							,1
							,0
						)
					)
					,"{MM/DD/CCYY}"
				)
			)
			-1 // Will put us in the last day of the month we want
		)
		,"{MM/DD/CCYY}"
	) 
)

DSguru2B · Post by **DSguru2B** » Fri May 19, 2006 2:25 pm

Sweet

As Craig mentioned, the OP, in another post, verified that it was a server question. But i am sure your solution will help others in the future.
Once again, Nice

janhess · Post by **janhess** » Mon May 22, 2006 3:53 am

We still have the problem of what to do with 29/02/2004.
In your example if I add 9 months I get 29/11/2005. A year out and should the month be at the month end?
In fact all years are 1 year out where the year should not change.

Mine's got bugs as well

jgibby · Post by **jgibby** » Mon May 22, 2006 6:13 am

You're right. I hadn't caught that bug. I'll have to fix that to save face... again! LOL

janhess · Post by **janhess** » Mon May 22, 2006 6:15 am

OK I think this does everything but I'm sure John will find something wrong.

Output is text in DD/MM/CCYY format.

Code: Select all

=
//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)
)

jgibby · Post by **jgibby** » Mon May 22, 2006 7:08 am

Okay, I think I fixed the BUG that Jan found! LOL Here is the corrected formula.

Code: Select all

=EITHER(
	FROMDATETIME(
		TODATETIME(
			// Month
			FILLLEFT(
				NUMBERTOTEXT(
					MOD(
						TEXTTONUMBER(
							FROMDATETIME(DateValue,"{MM}")
						)
						+(IF(
							TEXTTONUMBER(TextField Fields:MonthsToAdd)<0
							,-1
							,1
						)
						* if(
								mod(TEXTTONUMBER(TextField Fields:MonthsToAdd),12)=0
								,12
								,mod(TEXTTONUMBER(TextField Fields:MonthsToAdd),12)
							)
						)
						+IF(TEXTTONUMBER(TextField Fields:MonthsToAdd)<0,12)
						,12
					)
					+IF(
						MOD(
							TEXTTONUMBER(
								FROMDATETIME(DateValue,"{MM}")
							)
							+TEXTTONUMBER(TextField Fields:MonthsToAdd)
							+IF(TEXTTONUMBER(TextField Fields:MonthsToAdd)<0,12)
							,12
						)=0
						,12
						,0
					)
				)
				,"0"
				,2
			)
			+ "/" // Separator
			// Day of Month
			+ FROMDATETIME(DateValue,"{DD}")
			+ "/" // Separator
			// Year
			+ NUMBERTOTEXT(
				TEXTTONUMBER(
					FROMDATETIME(DateValue,"{CCYY}")
				)
				+ TRUNCATE(
					(
						TEXTTONUMBER(
							FROMDATETIME(DateValue,"{MM}")
						)
						+TEXTTONUMBER(TextField Fields:MonthsToAdd)
						-12
						+IF(
							TEXTTONUMBER(TextField Fields:MonthsToAdd) > 0
							&
							(
								TEXTTONUMBER(
									FROMDATETIME(DateValue,"{MM}")
								)
								+TEXTTONUMBER(TextField Fields:MonthsToAdd)
							) > 12
							,-1
							,0
						)
					)
					/12
				)
				+IF(
					TEXTTONUMBER(TextField Fields:MonthsToAdd) > 0
					&
					(
						TEXTTONUMBER(
							FROMDATETIME(DateValue,"{MM}")
						)
						+TEXTTONUMBER(TextField Fields:MonthsToAdd)
					) > 12
					,1
					,0
				)
			)
			,"{MM/DD/CCYY}"
		)
		,"{MM/DD/CCYY}"
	)
 	// Since the resulting date is not valid, we will increment 
 	// one month, set the day of the month to the 1st, and then 
 	// convert it to a date.  Then subtract one day and convert 
 	// it back to a date.  This will put us on the last valid 
 	// day of the target month. 
 	,FROMDATETIME(
		NUMBERTODATE(
			DATETONUMBER(
				TODATETIME(
					// Month
					FILLLEFT(
						NUMBERTOTEXT(
							MOD(
								TEXTTONUMBER(
									FROMDATETIME(DateValue,"{MM}")
								)
								+(IF(
									TEXTTONUMBER(TextField Fields:MonthsToAdd)<0
									,-1
									,1
								)
								* if(
										mod(TEXTTONUMBER(TextField Fields:MonthsToAdd),12)=0
										,12
										,mod(TEXTTONUMBER(TextField Fields:MonthsToAdd),12)
									)
								)
								+IF(TEXTTONUMBER(TextField Fields:MonthsToAdd)<0,12)
								+ 1 // Next Month Increment
								,12
							)
							+IF(
								MOD(
									TEXTTONUMBER(
										FROMDATETIME(DateValue,"{MM}")
									)
									+TEXTTONUMBER(TextField Fields:MonthsToAdd)
									+IF(TEXTTONUMBER(TextField Fields:MonthsToAdd)<0,12)
									+ 1 // Next Month Increment
									,12
								)=0
								,12
								,0
							)
						)
						,"0"
						,2
					)
					+ "/" // Separator
					// Day of Month
					+ "01" // First Day of Next Month 
					//+ FROMDATETIME(DateValue,"{DD}")
					+ "/" // Separator
					// Year
					+ NUMBERTOTEXT(
						TEXTTONUMBER(
							FROMDATETIME(DateValue,"{CCYY}")
						)
						+ TRUNCATE(
							(
								TEXTTONUMBER(
									FROMDATETIME(DateValue,"{MM}")
								)
								+TEXTTONUMBER(TextField Fields:MonthsToAdd)
								+ 1 // Next Month Increment
								-12
								+IF(
									TEXTTONUMBER(TextField Fields:MonthsToAdd) > 0
									&
									(
										TEXTTONUMBER(
											FROMDATETIME(DateValue,"{MM}")
										)
										+TEXTTONUMBER(TextField Fields:MonthsToAdd)
									) > 12
									,-1
									,0
								)
							)
							/12
						)
						+IF(
							TEXTTONUMBER(TextField Fields:MonthsToAdd) > 0
							&
							(
								TEXTTONUMBER(
									FROMDATETIME(DateValue,"{MM}")
								)
								+TEXTTONUMBER(TextField Fields:MonthsToAdd)
							) > 12
							,1
							,0
						)
					)
					,"{MM/DD/CCYY}"
				)
			)
			-1 // Will put us in the last day of the month we want
		)
		,"{MM/DD/CCYY}"
	)
)

janhess · Post by **janhess** » Mon May 22, 2006 7:13 am

Which ever version you choose to use I would hate to have to maintain a map with rules like them.

jgibby · Post by **jgibby** » Mon May 22, 2006 7:31 am

Yeah, they can be a pain. Especially too if you have to use the same formula in multiple places in the map and/or in multiple maps. Ugh. Sounds like a feature/enhancement request in the making.

John

janhess · Post by **janhess** » Fri Nov 03, 2006 10:52 am

Can someone turn this into a function at 8.1 using the New Function fascility?