Choose Language Hide Translation Bar
Highlighted
jfiori1
Level I

How can I edit the date difference formula for missing data?


How can i edit the Date Difference formula to return a missing data field if one of the dates is missing.  Now it returns -2147483648 if a date is missing.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
ms
Super User ms
Super User

Re: How can I edit the date difference formula for missing data?

In a data table this column formula should work:

 

If( !Is Missing( :Date1 + :Date2 ),
  Date Difference( :Date1, :Date2, "Day" )
)

 

 

In a more general (scripting) context, where the date arguments may be expressions I think this approach works:

 

DD = Expr( Date Difference( Date DMY( 1, 1, Year( Today() ) ), Today(), "Day" ) );
If( Is Missing( Eval( Arg( DD, 1 ) ) ) | Is Missing( Eval( Arg( DD, 2 ) ) ),
  .,
  DD
);

 

 

View solution in original post

3 REPLIES 3
Highlighted
ms
Super User ms
Super User

Re: How can I edit the date difference formula for missing data?

In a data table this column formula should work:

 

If( !Is Missing( :Date1 + :Date2 ),
  Date Difference( :Date1, :Date2, "Day" )
)

 

 

In a more general (scripting) context, where the date arguments may be expressions I think this approach works:

 

DD = Expr( Date Difference( Date DMY( 1, 1, Year( Today() ) ), Today(), "Day" ) );
If( Is Missing( Eval( Arg( DD, 1 ) ) ) | Is Missing( Eval( Arg( DD, 2 ) ) ),
  .,
  DD
);

 

 

View solution in original post

Highlighted
jfiori1
Level I

Re: How can I edit the date difference formula for missing data?

The first option worked perfectly when I pasted it into the formula box.  I got the missing data dot for lines with missing dates.  Excellent.

Thanks,  JMF

Highlighted
Jeff_Perkinson
Community Manager Community Manager

Re: How can I edit the date difference formula for missing data?

As a result of this discussion, the Date Difference () function has propagated missing values since JMP 12.

-Jeff
Article Labels

    There are no labels assigned to this post.