7 Replies Latest reply on Mar 21, 2014 2:40 PM by DavidJondreau

# Unexpected result in Date Calc

Howdy all,

Ran into a date calc behavior I didn't expect...so I'm checking my expectations.

I have a manually entered date field, and I'm trying to calculate "first day of two months ago".

All works fine...but now the date is January (or Febraury).

The Calc for the date 'two months ago":

Date(

Month (DateField) - 2 ; 1 ; Year(Datefield)

)

Oddly enough, when I put in 1/31/2014, I get 11/1/2013.....why did I not get 11/1/2014 ?

Why does the calc of month, which I thought to be a standalone within the function, still affect the year which is another part of the same function?

If I did the subtraction outside of the Date() function I would have expected this...but inside of the date function I would have expected the month and year to be treated as discrete entities.

Do I have some syntax wrong somewehere?

Is this normal and expected behavior?

• ###### 1. Re: Unexpected result in Date Calc

your month results in a negative number ( 1 - 2 = -1 ) which is throwing off your entire calculation.

You should wrap it in a let() to account for the date adjustment outside.

Let ([

d = DateField - 60

];

date ( month(d) ; 1 ; year(d) )

)

• ###### 2. Re: Unexpected result in Date Calc

Again, this isn't a problem...just unexpected.

I can't use the "-60" since that is not always two months...but the point is taken just the same.

The result of -1 for the month helps to understand it...thanks!  I guess I was thinking in names rather than in math and misled myself.

Somehow I assumed that FMP would correct the  {  Month (1 - 2)   } to be 11 (November), rather than -1, prior to applying the Date() function...I appreciate the walkthrough.

File under "duh, why didn't I see that myself"

• ###### 3. Re: Unexpected result in Date Calc

If you read up on the date() function, you'll get a better understanding how FM treats negative numbers in a date-based function.

http://www.filemaker.com/13help/en/html/func_ref1.31.21.html#1028390

quoted:

Note  If you type a month greater than 12 or a day greater than the number of days in a month, FileMaker Pro adds the extra days or months to the result. The date function also allows zero and negative numbers as parameters. Decimal numbers are truncated to integers.

I've noticed on a few functions the math calculated is quite literal. It's a date function, but the parameters taken are straight up numbers. The result of Month() as well is no longer a date field, it's an integer.

Good luck!

• ###### 4. Re: Unexpected result in Date Calc

Let([

M= Month(dateField);

D= Day(dateField);

Y=Year(dateField)];

M-2&"/"&D&"/"&Y)

Validate as date field.

• ###### 5. Re: Unexpected result in Date Calc

Aryden,

that will not work, if you enter a date like 1/1/2014, it would not return 11/1/2013 as expected, given your calculation result isn't a date, it will return –1/1/2014.

Yet you're on the right track. If you change the result of your let statement to:

Date ( M-2 ; D; Y)

It would return 11/1/2013 as a result.

• ###### 6. Re: Unexpected result in Date Calc

Yeah, sorry it was a failur eon my part to copy/paste teh right calculation into the post. Thanks for the reminder.

• ###### 7. Re: Unexpected result in Date Calc

Date() has some really handy features, including this one. Try Date ( 3 ; 0 ; 2014 ).