turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- How can I edit the date difference formula for mis...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 12, 2014 11:38 AM
(2596 views)

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.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 12, 2014 12:47 PM
(4500 views)

Solution

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

**)**;

2 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 12, 2014 12:47 PM
(4501 views)

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

**)**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 13, 2014 5:54 AM
(2329 views)

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