My report currently looks like this:
The ‘Date’ column is pulled directly from the base query.
‘Days’ is a variable I created as follows:
=If ([Date] = "";"0";ToNumber(DaysBetween(ToDate([Date];"dd MMM yyyy");CurrentDate())))
The purpose of this variable is simply to calculate the number of days between the ‘Date’ value and today’s date.
The ‘Test’ column contains the following formula:
=If ([Days] Between("1";"42")) Then "n" Else "YES"
This formula is designed to result in “YES” if the value of ‘Days’ is between 1 and 42 inclusive.
All looks ok so far, except there are a few rogue results such as the middle row of the following which should be “n”:
I can’t work out why it is giving this unexpected result…
One assumption I have made is that it is nothing to do with the format of ‘Date’, otherwise the formula in ‘Days’ would throw errors or some other strange result. But if that isn’t the problem, what else could be the issue? Have I formed one of my formulae incorrectly?