Date Calc

[faaslave]

What would be the easiest way to have a date calc that adds six months to a date?

3/4/06 would result 9/4/06

Since the months have different lengths, a math one such as 6*30 is not working exactly right.

Thanks Dave

[MarkWilson]

Date ( Month ( DateField ) + 6 ; Day ( DateField ) ; Year ( DateField ) )

[Genx]

See, i forgot that function even existed.

[faaslave]

Thank you

Dave

[Mikhail Edoshin]

The typical problem with date math are extremes. What should the result be for March 31? The formula you're given will return 31th day of 9th month, which doesn't exist actually, because September has only 30 days, so the result will be Oct 1. Is this what you want or the answer should be September 30?

(Even more peculiar example is August 31; the result will be either March 2 or 3 depending on whether the next year is leap or not.)

http://fmforums.com/forum/showtopic.php?tid/179409/

[Genx]

******* date maths... Has anyone worked out a comprehensive CF for this?

[Lee Smith]

Hi Mikhail,

Your link pointed to this topic, was there a file or a different topic you wanted to point to instead?

Lee

[Mikhail Edoshin]

About the link to the same page: Lee, I'm sorry if this has confused you. I just face hard times trying to return to the topic I'm participating in. The email "updates" I get don't have any link to quickly go back to the topic, so I sometimes have to go through my browser's history and this doesn't work too well, because even when I find the link to what seems to be the post it could be a link to the reply page ("newpost.php") that has expired already. So I decided to insert a link to the post into at least my messages, which I can locate in my inbox. Bad idea, I see, at least without a remark. BTW, is there any forum feature I don't know about that could help in this?

[LaRetta]

******** date maths... Has anyone worked out a comprehensive CF for this?

FM can handle dates just fine by itself - it spans, jumps properly over month, year and leap-year boundaries with a single bound.

Mikhail, the Search popup contains a link to My Posts. :wink2:

Edited August 12, 2006 by Guest
Added note to Mikhail

[comment]

Has anyone worked out a comprehensive CF for this?

The problem is not in working this out, but rather in defining "this". This is not a calculation problem. As Mikhail pointed out, the meaning of "adds six months to a date" is not entirely clear. Strictly speaking, Mark's formula does exactly that. In some circumstances, a strict interpretation does not meet the actual requirements. In such case, the requirements need to be spelled out very clearly. For example, should adding a month to September 30 result in November 30 (the same day of month), or November 31 (last day of the month)? If you answered November 31, you also need to answer if September 29 should return November 30 (the day before the last day of the month). If yes, where does it end - assuming we don't want to count backwards from the end of the month for ANY date, so that September 15, for example, returns November 15 - not 16?

There are quite a number of possible answers here, and for each one the calculation would be different.

Here's an example of a formula that adds n months to a date, but does not cross over into the (n+1)th month:

Let ( [

newDate = Date ( Month ( date ) + n ; Day ( date ) ; Year ( date ) ) ;

limit = Date ( Month ( date ) + n + 1 ; 0 ; Year ( date ) )

] ;

Min ( newDate ; limit )

)

[Mikhail Edoshin]

Oh, found the link to "My Posts". Thank you very much : Finally found a topic I wanted to reply to a week ago.

[faaslave]

Sorry for all the confusion. I did mean 6 months later on the same day. Feb 7th, 2006 should return August 7th, 2006.

Thanks Dave

[Genx]

This is just a more commented out version of comments calc - i couldn't really work out where to place the variables so...:

Let ( [x = Get(CurrentDate) ; n = 4 ;

newDate = Date ( Month ( x ) + n ; Day ( x ) ; Year ( x ) ) ;

limit = Date ( Month ( x ) + n + 1 ; 0 ; Year ( x ) )

] ;

GetAsDate(Min ( newDate ; limit ))

)

These are the only two variables you change:

x = Your Date that you want to add months to.

n = number of months

[macmangler]

A logical initial approach may be to take the date you would like calculated 6 months later and add 180 days. (Date + 180)

However this simple result does not return the "exact day" 6 months later.

See attached files... in an extremely easy solution, I add 184 days to the date in one calculation. In a new calculation, I extract the Month and Year off my first calculation and use the day of the original date. This gets the day you wanted 6 months later and calculates the year... October 10, 06 returns April 10, 07.

Most geniuses in filemaker here may disagree with my approach.

But it works easily and no headaches.

SIX_MONTHS.zip

[macmangler]

Adding 183 or 184 days would work in my solution. 180 days would not work because a true 6 months average is 182.5 days, thus making January 1 return June 30. The correct month needed is July...not June. I went with 184 to be safe... I figured I'd compensate for leap year. : )

Edited August 13, 2006 by Guest
Typo

[macmangler]

I take back all my posts... my solution works, but Mark Wilson

has the winning idea!

Date ( Month ( DateField ) + 6 ; Day ( DateField ) ; Year ( DateField ) )

Easiest, simplest solution! I feel like a dumb ass for posting... but, there are so many ways to skin a cat! The forum is for sharing!

Thanks!

[faaslave]

Yes, Mark Wilsons calc worked perfect. I guess I should have made that clear.

Sorry.

Thanks Mark

I have plenty of other troubles though. I started a new topic, calcs w/portals. So I will end this thread and stay with that one.

Thanks to all

Dave

[Genx]

Mark Wilson's solution will return 6 months, the issue however lies is the following:

Attempt to use this :

Date (1 + 1 ; 31 ; 2006)

Suprise : It does not return the same effective day of month but instead rolls over to the third of march... and you know the saying... "beware the ides of march" - even though thats like the 15th or the 17th... irrelevant.

Let ( [[color:orange]x = Date( 1 ; 31 ; 2006) ; n = 1 ;

newDate = Date ( Month ( x ) + n ; Day ( x ) ; Year ( x ) ) ;

limit = Date ( Month ( x ) + n + 1 ; 0 ; Year ( x ) )

] ;

GetAsDate(Min ( newDate ; limit ))

)

Now paste the above, and see what is returned.

Anyway yeah, it will add a month, but the issue was relating to what happens if there arent that many days in that month -- there are other ways of doing it like using the month number to determine if it's odd or even with the exception of february... but comments way is sooooo much easier.