Same formula - different result in other workbook

[nielf]

I have created formula to determine if a date falls is between two dates.

The formula looks like this (in a Table):

=AND([@CalcDatesMerged]>EOMONTH(TODAY();-14);[@CalcDatesMerged]<=EOMONTH(TODAY();-1))

I tested the formula in a dummy workbook and the result was like I expected it to be. 29-02-2016 is FALSE as it is supposed to be.

However, when I copy the formula to another workbook the result is no longer the same. For some reason 29-02-2016 is now TRUE:

It is the exact same formula but with two different results. For some reason the last day of the month is included in the second example and I have no idea why.

Are there any date settings or something else that could be causing this - or am I just missing something obvious?

 

[wideboydixon]

I would do the following:

- Select cell F6472
- Go to the formulas tab
- Evaluate the formula

If I was to guess, I'd say the value in column E has a time component as well (e.g. "29-02-2016 01:00:00"). Try selecting E6472 and changing the cell format to General or Number. Perhaps it's not a whole number? If that's the case then your formula should be:

=AND(INT([@CalcDatesMerged])>EOMONTH(TODAY();-14);INT([@CalcDatesMerged])<=EOMONTH(TODAY();-1))

WBD

 

[nielf]

I would do the following:

- Select cell F6472
- Go to the formulas tab
- Evaluate the formula

If I was to guess, I'd say the value in column E has a time component as well (e.g. "29-02-2016 01:00:00"). Try selecting E6472 and changing the cell format to General or Number. Perhaps it's not a whole number? If that's the case then your formula should be:

=AND(INT([@CalcDatesMerged])>EOMONTH(TODAY();-14);INT([@CalcDatesMerged])<=EOMONTH(TODAY();-1))

WBD

Thank you very much for your input. I tried using your formula and that worked as charm.

You saved me from getting grey hair

 

[wideboydixon]

You're welcome. Sometimes a second pair of eyes is a real help

WBD