cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
fr2007
Level II

Calculating duration between two dates from different years

I am using JSL scripting to run one of my reports that can calucate the duration between two dates. My report has a Start Date in column 'A' and End Date in column 'E' , I want to calcuate the following:

1. Duration between those two days excluding weekends

2, Duration between those two days , start date is 10/31/2012 and end date is 02/20/2013, and when I used the forumla "Formula(Day(:E - :A) )" , Iam getting a value of 10, instead of something bigger. Start Date is in 2012 and end date is in 2013, so do I need to make any change to my script ? The other durations that fall in the same year are calculated properly using the same formula..but the dates that fall in different years are not calculated accurately..

can some one help with the two questions?

thanks,

13 REPLIES 13
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Calculating duration between two dates from different years

1. This column formula should give the number of working days between the two days:

Try( Sum( Day Of Week( Index( :A, :E, 86400 ) ) < 6 ) - 1 )

Paste this into the Formula editor. The Try() is only needed if you have empty cells in the date column. The -1 is there to exclude the start (or end) date from the sum. 86400 is the number of seconds in 24 hours.

2. The column formula equivalent to PMroz suggestion would be:

(:E - :A) / 86400

louv
Staff (Retired)

Re: Calculating duration between two dates from different years

How about using the Column Info and specify Start Date  and End Date columns with Data Type = Numeric, Modeling Type = Continuous, Format=Date=m/d/y, Input Format=Date=m/d/y. Then add an additional column and using the Column Formula specify End Date-Start Date and also choose Format Duration :day:hr:min

Byron_JMP
Staff

Re: Calculating duration between two dates from different years

If your start and end date columns are Continuous, Numeric, Date format; then the formula for the duration between the two columns in days could be something like:   Date Difference(:Start Date, :End Date, "day")

If your date columns are character, then you can parse the text like PMroz did above with:

Date Difference(

informat(:Start Date, "m/d/y"),

informat(:End Date, "m/d/y"),

"day")

JMP Systems Engineer, Health and Life Sciences (Pharma)
fr2007
Level II

Re: Calculating duration between two dates from different years

This is working , but I dont want to include weekends in my calcuation..Is there any way to calculate only weekdays?