LastDayOfMonth ( theDate, theDifference )

[Kent Searight]

Name & Parameters: [color:red][big] LastDayOfMonth ( theDate, theDifference ) [/big]

Description: Returns the date of the last day of a month, measured from the date (theDate) plus the number of months in the future (theDifference) from that date. All positive, whole numbers and zero are valid for “theDifference"

Sample Input:

LastDayOfMonth ( 12/2/2007 ; 2 )

Results:

2/29/2008

Recursive: no

Formula:

Let (

[ 



//The number of months ahead



Dif = theDifference ;



//The number of the month that's Dif months ahead + 1



endMonthPlusOne = Case (



Mod ( Month ( theDate ) + Dif ; 12 ) + 1 ≤ 12 ;

Mod ( Month ( theDate ) + Dif ; 12 ) + 1 ;

1

) ;



//The year of the month that's Dif months ahead + 1



endYearOfEndMonth = Case (



Month ( theDate ) + Dif + 1 ≤ 12 ;

Year ( theDate ) ;

Floor ( ( Dif + Month ( theDate ) + 1 ) / 12 ) + Year ( theDate )



)

];



GetAsDate ( endMonthPlusOne & "/1/" & endYearOfEndMonth ) - 1



)



/*Kent Searight, 2008

3rd Rock Data, LLC*/ 

Required Functions: N/A

Author(s): Kent Searight

Date: 02/27/08

Credits:

Disclaimer:

FM Forums does not endorse or warrantee these files are fit for any particular purpose. Do not post or distribute files without written approval from the copyright owner. All files are deemed public domain unless otherwise indictated. Please backup every file that you intend to modify.

[comment]

How about a simple one:

Date ( Month ( theDate ) + theDifference + 1 ; 0 ; Year ( theDate ) )

BTW, this works with any date format you happen to be using, unlike GetAsDate().

[Kent Searight]

I didn't know one could use a number greater than 12 for the month or a number less than 1 for the day.

Thanks for showing me this!

[comment]

You're not the only one:

http://www.fmforums.com/forum/showtopic.php?tid/177012/post/207066/#207066

But yes, you can - that's what makes the Date() function so powerful.

[Kent Searight]

That's an interesting thread you refer to, Michael.

Well, now that I've recovered from the wind being taken out of my sails I'm pretty excited about this newfound knowledge. I can think of a couple more occasions in the past when I've unnecessarily complicated date calculations because I didn't realize the flexibility of the Date function.

Onward ho to smoother sailing! :