need some help with a date function...

[MParker]

this one is blowing my mind!!! so, i need to calculate a date that is 11 months in the past. this is what i'm doing:

Date ( Month ( Get (CurrentDate) ) + 1 ; 1 ; Year ( Get (CurrentDate) ) - 1 )

i've also tried:

Let ( [ M = Month ( Get ( CurrentDate ) ) + 1;

Y = Year ( Get ( CurrentDate ) ) - 1;

D = 1 ] ;

Date ( M ; D ; Y ) )

but for some really weird reason, it's making the year as 2011 (which should be 2010 by my calculations). here's the weirder thing....if i change my system date back 1 month....the calculation works fine. so, when my system date is set to december 2011...no worky.....but november 2011....works. ???

can anyone clue me into what the "proper" function should be in order to find a date exactly 11 months back from the current date?

[LaRetta]

Date ( Month ( Get (CurrentDate) ) - 11 ; 1 ; Year ( Get (CurrentDate) ) )

although if you want exactly 11 months back then the day must be the current dates day as well. And the calculation must be checked 'do not store' in storage options or it will not update.

[jbante]

Both of the calculations you posted work fine when I tried them. Are you using a version of FileMaker for a country with a D/M/Y date format? For that matter, do international versions of FileMaker change the order of arguments of the Date () function at all? Since I don't have a non-US copy of FileMaker, I have no idea. However, I would've written the calculation like this, in case it helps:

Let ( ~today = Get ( CurrentDate ) ; Date ( Month ( ~today ) - 11 ; 1 ; Year ( ~today ) ) )

FileMaker is smart enough to know that when "Month ( ~today ) - 11" works out to a negative number, you mean the previous year.

[comment]

The problem seems to be more with your expectations than with the actual results. If today is December 2011, then a date 11 months ago is in January 2011. If you want the exact date (subject to differences in month lengths), then use Day ( Get ( CurrentDate ) ) instead of 1. But other than that, your calculation is fine - except it could be simplified to =

Date ( Month ( Get (CurrentDate) ) -11 ; 1 ; Year ( Get (CurrentDate) ) )

as suggested by LaRetta.

do international versions of FileMaker change the order of arguments of the Date () function at all?

No.

Edited December 8, 2011 by comment

[MParker]

The problem seems to be more with your expectations than with the actual results. If today is December 2011, then a date 11 months ago is in January 2011. If you want the exact date (subject to differences in month lengths), then use Day ( Get ( CurrentDate ) ) instead of 1. But other than that, your calculation is fine - except it could be simplified to =

o....m......g......i'm feeling rather stupid right now. you're right. you're absolutely right. i have NO idea why i was thinking 11 months was going to be in 2010.....i really feel stupid right now. thank you. all of you for your help.

[jkluchnik]

The problem here is that if you go back 11 months and end up in February, you could end up with an invalid date, such as 31.2.11 (please excuse me if I am wrong here on somebody's solution above, I have not tried them, just looked). You can solve this problem to give you exactly the same day of the month, unless it does not exist. Then it goes to the last day of that month. This function exists in Excel as 'EDATE' and we have created a custom function to replicate that:

EDATE ( evalDate ; months )

Let ( [

~targetDate = Date ( Month ( evalDate ) + months; Day ( evalDate ); Year ( evalDate ) ) ;

~EOM = Date ( Month ( evalDate ) + 1 + months; 0; Year ( evalDate ) )

];

Min (~targetDate ; ~EOM )

)

So for your purposes, you would use it as: EDATE ( Get ( CurrentDate ) ; -11 )

[LaRetta]

The problem here is that if you go back 11 months and end up in February, you could end up with an invalid date, such as 31.2.11 (please excuse me if I am wrong here on somebody's solution above, I have not tried them, just looked).

Actually, that is incorrect. When you use the FileMaker date functions, they adjust properly for leap year; no invalid date possible. :wink3:

[jkluchnik]

I was just looking at this too deeply and making some assumptions that perhaps are not correct. I am in finance and when we "subtract 1 month", we are typically looking for the same date in the month, or an adjustment that EDATE does. In the case of your formula above, when inputing the date of 31.1.2012, it results in 3.3.2011, where I would have wanted 28.2.2011.

However, I guess I was trying to be too smart and didn't bother to notice that MParker wanted a result on the 1st of the month. In that case, all the above is correct. However, in the scenario that one would want the exact date as comment talks about, then it depends what the expectations are. I guess it all depends how one would define 1 month.

[LaRetta]

Hi jkluchnic,

My apology if I was unclear. I was not speaking about what solution we provided for mparker but rather this incorrect statement:

"you could end up with an invalid date, such as 31.2.11"

If using FM date functions, you cannot ever end with an invalid date of 31.2.11. FM adjusts it to 2.3.11 which is not invalid. It does not matter whether you use minus dates, minus years or calculations such as Mod(). FMs date engine can handle it properly. :laugh2:

[jkluchnik]

Actually my fault. I should not have use the term "invalid date". I just meant incorrect, depending on the desired result. I am just learning about the complexities of the date engine. Very interesting. If you look at the function I wrote, you will see that it has a variable EOM. That is actually another function we use in-house to get the end of month. I use the day "0" which filemaker interperates as the last day of the previous month. Pretty useful stuff once you understand it.

Lately, we have been building custom functions in-house to replicate things that Excel has, as we are building a very complex system to replace portfolio analysis that is traditionally done in Excel. Many of the issues were things like dates. So we built the functions EDATE and EOMONTH from excel, using the stuff above.

Now, if only someone would find a way to do IRR and XIRR completely in Filemaker, I would be a happy camper!

[comment]

Hi Jorge,

you are right when you say that the "correct' answer depends on one's expectations; that's why I said "subject to differences in month lengths".

See also:

http://fmforums.com/forum/topic/45376-date-calc/page__view__findpost__p__212014

http://fmforums.com/forum/topic/59942-frequency/page__view__findpost__p__284146