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. Even then there was always the risk of someone getting a format issue. 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. 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. The very fact that JMP, unlike Excel, has a duration time format has been a godsend for us. Due to the nature of our work, we deal with timing data extensively. 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. The following formula returns 1, the number of times a new hour started between the times:ĭate Difference(31Dec2010:23:59:59, 01Jan2011:00:59:59, 'Hour', 'start') Īlternatively you can run the following script and pay attention to the last comand with the formula of column 3.įormat( 'Best', Use thousands separator( 0 ), 17 ),įormula( Date Difference( :Column 1, :Column 2, 'day', 'actual' ) ) The following formula returns 9, the number of completed hours between the times:ĭate Difference(01Jan2010:00:00:00, 01Jan2010:09:22:57, 'Hour', 'actual')

The following formula returns 207, the number of completed days between the dates:ĭate Difference(01Jan2010:00:00:00, 27Jul2010:21:21:57, 'Day', 'actual')

Start is used to count the number of times an interval starts.įractional is used to count fractional intervals.įor example, the following formula returns 207.890243055556, the number of days between the dates:ĭate Difference(01Jan2010:00:00:00, 27Jul2010:21:21:57, 'Day', 'fractional') The alignment arguments are described here: The interval argument can be Second, Minute, Hour, Day, Week, Month, Quarter, Year. Returns the difference of two datetime values. it is called Date difference and you can find it in the formula editor under 'Date Time' functions. There is a designated formula just for that.

Excel number of days between dates