Fund Manager
PORTFOLIO MANAGEMENT SOFTWARE
Contact Us

Custom Report - comparing date fields in an IF Function

Questions on using, creating, or understanding data in Fund Manager reports.

Postby Allan » Fri Mar 19, 2021 5:58 am

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?
Allan
 
Posts: 19
Joined: Sat Jan 08, 2011 7:14 pm

Postby Mark » Fri Mar 19, 2021 9:23 am

Hi Allan,

What are you trying to validate about the date? If you get an N/A, it isn't a valid date.
Thanks,
Mark
Fund Manager - Portfolio Management Software
Mark
Site Admin
 
Posts: 11252
Joined: Thu Oct 25, 2007 2:24 pm
Location: Chandler, AZ

Postby Allan » Fri Mar 19, 2021 10:38 am

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?
Allan
 
Posts: 19
Joined: Sat Jan 08, 2011 7:14 pm

Postby Mark » Fri Mar 19, 2021 1:59 pm

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.
Thanks,
Mark
Fund Manager - Portfolio Management Software
Mark
Site Admin
 
Posts: 11252
Joined: Thu Oct 25, 2007 2:24 pm
Location: Chandler, AZ

Postby Allan » Fri Mar 19, 2021 5:11 pm

Thanks Mark

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

Allan
Allan
 
Posts: 19
Joined: Sat Jan 08, 2011 7:14 pm


Return to Reports

Who is online

Users browsing this forum: No registered users and 11 guests

FundManagerSoftware.com | Search | Site Map | About Us | Privacy Policy
Copyright © 1993-2024 Beiley Software, Inc. All rights reserved.
cron