topic How do you calculate number of days between two dates? in Discussions

How do you calculate number of days between two dates?

goutam — Thu, 31 Aug 2017 09:10:35 GMT

If I have two date fields, both with m/d/y width 12 formats, how do I get the difference in number of days? Is there an easy way to do this?

Re: how do you calculate number of days between two dates?

scwise — Wed, 19 Oct 2016 00:27:04 GMT

Goutam,

Thanks for the question!

To get the difference in days between two dates in JMP (in D/M/Y format), you can easily do the following:

Easiest Way: Delta (Days)

Step 1: Create a new column in your data table

Step 2: Subtract the first date from the second date and divide this by 86,400

JMP returns the date difference in seconds, so 86,400 is the total seconds in a day to get this convereted into a count of days.

See below for the formula.

AlternativeWay: Delta (D:H:M) Time Duration

Step 1: Create a formula in a new column in your data table

Step 2: Just subtract the first date from the second date

Step 3: In the column properties, under format, select Duration day:hr:m from the drop down list

This will return the difference in day count, plus any remaining hours and minutes.

See below for the formula and format:

Hope this helps!

Scott

Re: how do you calculate number of days between two dates?

ms — Mon, 18 May 2020 13:58:55 GMT

Date values are stored internally as the number of seconds lapsed from a reference date (January 1, 1904). Hence, the difference between two dates in days can be calculated by:

(:Date2 - :Date1) / (60 * 60 * 24)

There is also a function Date Difference() in JMP 12 (may have been there in earlier versions too, I didn't notice it until now). It does the same thing as far as I can tell, but it has some nice options (look them up the Scripting Index, Help Menu).

Date Difference(:Date1, :Date2, "Day")

Re: how do you calculate number of days between two dates?

ron_horne — Thu, 31 Aug 2017 09:15:12 GMT

Hi Goutam,

there is a designated formula just for that. it is called Date difference and you can find it in the formula editor under "Date Time" functions.

the following is from the help files:

Date Difference

Returns the difference of two datetime values. The interval argument can be Second, Minute, Hour, Day, Week, Month, Quarter, Year. The alignment arguments are described here:

Start is used to count the number of times an interval starts.

Actual is used to count whole intervals.

Fractional is used to count fractional intervals.

For example, the following formula returns 207.890243055556, the number of days between the dates:

Date Difference(01Jan2010:00:00:00, 27Jul2010:21:21:57, "Day", "fractional");

The following formula returns 207, the number of completed days between the dates:

Date Difference(01Jan2010:00:00:00, 27Jul2010:21:21:57, "Day", "actual");

The following formula returns 9, the number of completed hours between the times:

Date Difference(01Jan2010:00:00:00, 01Jan2010:09:22:57, "Hour", "actual");

The following formula returns 1, the number of times a new hour started between the times:

Date Difference(31Dec2010:23:59:59, 01Jan2011:00:59:59, "Hour", "start");

alternatively you can run the following script and pay attention to the last comand with the formula of column 3.

New Table( "Untitled",
      Add Rows( 1 ),
      New Column( "Column 1",
            Numeric,
            Continuous,
            Format( "m/d/y", 12 ),
            Input Format( "m/d/y" ),
            Set Values( [2900620800] )
      ),
      New Column( "Column 2",
            Numeric,
            Continuous,
            Format( "m/d/y", 12 ),
            Input Format( "m/d/y" ),
            Set Values( [2900966400] )
      ),
      New Column( "Column 3",
            Numeric,
            Continuous,
            Format( "Best", Use thousands separator( 0 ), 17 ),
            Formula( Date Difference( :Column 1, :Column 2, "day", "actual" ) )
      )
);

good luck!

Re: how do you calculate number of days between two dates?

louv — Wed, 19 Oct 2016 00:27:14 GMT

Goutam,

My pedestrian attempt gave a solution. Use column formula and subtract column 2 from columns 1 and then format columns 3 to display in days.

Re: how do you calculate number of days between two dates?

Steven_Moore — Mon, 24 Aug 2015 11:31:43 GMT

I find it much easier to do the math between dates in Excel and then transfer the results into a JMP table. I know this sounds like blasphemy, but it is much easier and faster than trying to figure out the scripting needed.

Re: how do you calculate number of days between two dates?

mdawson69 — Wed, 19 Oct 2016 00:28:46 GMT

Smoore2,

I would not go as far as saying that your statement is blasphemy, but in my experience, JMP is orders of magnitude better at handling date-times than Excel. Due to the nature of our work, we deal with timing data extensively. The very fact that JMP, unlike Excel, has a duration time format has been a godsend for us. I have lost count of the number of times that we have had to redo calculations in the past using Excel because of date-time format issues, but those problems disappeared once my analytical team began using JMP for analytics and became knowledgeable of how JMP handles date-times.

I have battled with date-time data in Excel for over a decade and often have to resort to VBA scripting to overcome Excel's shortcomings. Even then there was always the risk of someone getting a format issue. In JMP I have never needed to script date-time computations as most things can be handled by either using a simple formula and setting the format of a column or otherwise resolved with the extensive set of date-time functions JMP provides for column formulas. The screenshot below shows what the original poster wanted using three different methods already mentioned in this thread and required no scripting whatsoever.

The first difference uses a simple formula, (Date 2 - Date 1) / 86400, to compute the difference in days. Given that JMP stores date-times as a seconds count from 1 JAN 1904 12:00:00 AM, getting the number of days is just that simple. This method is useful for someone that wants any portion of days reported in decimal form.

The second difference is even simpler as it does not require knowing the number of seconds in a day. The formula used is just Date 2 - Date 1, and the column is formatted as a duration (:day:hr:m). Using this method reports the number of days in time format show remaining time is shown as hours, minutes, and seconds.

The last method uses the Date Difference function and returns just the number of days. Admittedly, Excel does this by default if you subtract Date 1 from Date 2, but what JMP sacrifices by not just spitting out the number of days from Date 2 - Date 1 more than makes up for Excel's foibles with date-time computations.

Re: how do you calculate number of days between two dates?

Anonymous — Thu, 27 Aug 2015 14:46:25 GMT

Although JMP has its strengths, one place that Excel excels over JMP is in easy data conversion. (It's a trade-off: it seems that Excel is full of AI-like features where it tries to guess what you want, whereas JMP's behavior is more predictable.) Conversion of dates is a great example. When I started working with JMP, it took me a while to realize that the date formats that JMP recognizes weren't just examples, but an enumerated list of the ONLY ones it understood. Excel allows you to build custom date input formats using the h, m, s, d, y characters along with delimiters, and it converts text into the internal timestamp format.

Re: how do you calculate number of days between two dates?

Bass_Masri — Tue, 12 Nov 2019 23:30:42 GMT

Date Difference works perfectly.