Custom Report - comparing date fields in an IF Function

[Allan]

I am using the Custom Reports and have a question about comparing dates.

Using the Nextincome_date in an IF statement, if the date is absent I get N.A. as a result. I am attempting to compare the date to determine if it is absent in which case I want to calculate a date instead.

I have been attempting to compare the following date fields and appear to have the same issue:
Nextincome_date, Last_dist, and a custom date field which I have imported.

Is there a way to validate a date field without producing the N.A. result?

[Mark]

Hi Allan,

What are you trying to validate about the date? If you get an N/A, it isn't a valid date.

[Allan]

I am trying to validate if the field contains a valid date and is not blank.


Example:
IF(Nextincome_date>Startdays(),Nextincome_date, "a formula")
When Nextincome_date is blank the function appears to return N/A and the formula is not executed

However, if I use the following
IF(Investment_type==4, Nextincome_date, "a formula")
Nextincome_date will be displayed for Bonds and if blank a blank is used (not N/A), and the formula is used for all items not equal to a bond

Is the use within the condition test causing the problem?
Is there a means to check if a date is valid?

[Mark]

Hi Allan,

Yes, the use within the comparison is the problem. If the date isn't a valid date, the comparison fails. It can't evaluate N/A, so the result will be N/A. The first example will allow you to see whether it is a valid date or not, by displaying either N/A or Nextincome_date, but you won't be able to execute "a formula" in this case, as it doesn't make it past the comparison stage when the date isn't valid.

Unfortunately, I don't think there is a way to do what you're asking. If a date isn't available, this ends up giving N/A, so you can't then do some other calculation. Maybe we could add some new function, like ISVALID() that you could put inside the comparison.

[Allan]

Thanks Mark

Yes an Invalid() function would be very useful for date related comparisons and validations. Hopefully in a future update.

Allan