# Date Calc

This topic is 5596 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

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

##### Share on other sites

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

##### Share on other sites

See, i forgot that function even existed.

Thank you

Dave

##### Share on other sites

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/

##### Share on other sites

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

##### Share on other sites

Hi Mikhail,

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

Lee

##### Share on other sites

******** 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 by Guest
##### Share on other sites

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 )

)

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

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:

n = number of months

##### Share on other sites

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

##### Share on other sites

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 by Guest
Typo
##### Share on other sites

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!

##### Share on other sites

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

##### Share on other sites

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.

##### Share on other sites

This topic is 5596 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account

×
×
• Create New...