nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

jfiori1

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.

Accepted answers

In a data table this column formula should work:

If( !Is Missing( :Date1 + :Date2 ),
&nbsp; 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 ) ) ),
&nbsp; .,
&nbsp; DD
);

All comments

In a data table this column formula should work:

If( !Is Missing( :Date1 + :Date2 ),
&nbsp; 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 ) ) ),
&nbsp; .,
&nbsp; DD
);

jfiori1

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

Jeff_Perkinson

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