Jump to content

Date Calc


faaslave
 Share

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

Link to comment
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/

Link to comment
Share on other sites

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?

Link to comment
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
Added note to Mikhail
Link to comment
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 )

)

Link to comment
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:

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

n = number of months

Link to comment
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

Link to comment
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
Link to comment
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!

Link to comment
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

Link to comment
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.

Link to comment
Share on other sites

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.