DSXchange

A short text to describe your forum
http://dsxchange.com/

specific function to add months in a date

http://dsxchange.com/viewtopic.php?f=43&t=100684

Page 2 of 2

Posted: Thu May 18, 2006 7:12 am
by chulett
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.

Posted: Thu May 18, 2006 7:36 am
by janhess
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) 
)

Posted: Thu May 18, 2006 11:43 pm
by chulett
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. :?

Posted: Fri May 19, 2006 2:21 pm
by jgibby
Well, I finally finished a formula that will (according to my rigorous :roll: 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}"
	) 
)

Posted: Fri May 19, 2006 2:25 pm
by DSguru2B
Sweet 8)
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 :wink:

Posted: Mon May 22, 2006 3:53 am
by janhess
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 :oops:

Posted: Mon May 22, 2006 6:13 am
by jgibby
You're right. I hadn't caught that bug. I'll have to fix that to save face... again! LOL

Posted: Mon May 22, 2006 6:15 am
by janhess
OK I think this does everything but I'm sure John will find something wrong. 8)
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)
)

Posted: Mon May 22, 2006 7:08 am
by jgibby
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}"
	)
)

Posted: Mon May 22, 2006 7:13 am
by janhess
Which ever version you choose to use I would hate to have to maintain a map with rules like them.

Posted: Mon May 22, 2006 7:31 am
by jgibby
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

Posted: Fri Nov 03, 2006 10:52 am
by janhess
Can someone turn this into a function at 8.1 using the New Function fascility?
All times are UTC-06:00
Page 2 of 2

Powered by phpBB® Forum Software © phpBB Limited