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
- :
- Calculating duration between two dates from different years

Topic Options

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Feb 20, 2013 9:22 AM
(7405 views)

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,

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Ok I think I understand the problem. JMP apparently does not follow the ISO 8601 standard (i.e first day of week is monday) but the US tradition of celebrating sunday as the first day of the week. This perfectly explains your results above as my formula assumes the former. However, it's trivial to adapt the formula to take that into account (or any other local definition of day of week or what days that are considered belonging to the weekend).

Try e.g. this:

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

Below is my test output and it appears to work. The "counter" halts over weekends (sat-sun where I live). Remove the -1 if you by "duration between two dates" mean to include the full duration of both start and end dates. (Here A = today, E = future days)

A | E | Weekday | Working days |

2013-04-05 | 2013-04-05 | Friday | 0 |

2013-04-05 | 2013-04-06 | Saturday | 0 |

2013-04-05 | 2013-04-07 | Sunday | 0 |

2013-04-05 | 2013-04-08 | Monday | 1 |

2013-04-05 | 2013-04-09 | Tuesday | 2 |

2013-04-05 | 2013-04-10 | Wednesday | 3 |

2013-04-05 | 2013-04-11 | Thursday | 4 |

2013-04-05 | 2013-04-12 | Friday | 5 |

2013-04-05 | 2013-04-13 | Saturday | 5 |

2013-04-05 | 2013-04-14 | Sunday | 5 |

2013-04-05 | 2013-04-15 | Monday | 6 |

2013-04-05 | 2013-04-16 | Tuesday | 7 |

2013-04-05 | 2013-04-17 | Wednesday | 8 |

2013-04-05 | 2013-04-18 | Thursday | 9 |

2013-04-05 | 2013-04-19 | Friday | 10 |

2013-04-05 | 2013-04-20 | Saturday | 10 |

2013-04-05 | 2014-04-21 | Monday | 271 |

2013-04-05 | 2015-04-22 | Wednesday | 533 |

13 REPLIES 13

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Calculating duration between two dates from different years

Can you provide more information? What is the data type and format of the A and E columns? Also, when you subtract dates you get the number of seconds between them. Here's a simple example that calculates the date difference in days:

adate = informat**(**"10/31/2012", "m/d/y"**)**;

edate = informat**(**"02/20/2013", "m/d/y"**)**;

cdate = **(**edate - adate**)** / **3600** / **24**;

This results in

**112**

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Calculating duration between two dates from different years

can you make it more general? I will explain my data clearly.. I have a column "Start Date" with format as Date (mm/dd/yyyy) and another column "End Date" with format as mm/dd/yyyy.I want to subtract start date from End date, my date are

Start Date End Date

10/31/2012 03/31/2013

09/15/2012 02/13/2013

08/15/2012 01/29/2013

Now I want to subtract End Date - Start Date to get the value in days

since start date is in 2012 and end dates are in 2013, iam not getting the correct value if I simply say :End Date - :Start Date

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Calculating duration between two dates from different years

This will give you time elapsed in days.

First, add a new numeric variable/column.

In the Formula Editor window, select first date, click subtract, select second date, then divided by, select Date Time function, in days, put a 1 in the parenthesis, click apply.

(:diagnosis_date - :dob_date) / In Days( 1 )

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Calculating duration between two dates from different years

This is calcuating same as using Date Difference..but this is including weekends as well..is there a way to calculate ONLY weekdays?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Calculating duration between two dates from different years

Did you ever try the formula in my reply? It was supposed to exclude weekdays and it works for me if the columns are in date format (not strings).

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Calculating duration between two dates from different years

I tried your formula..it is working for few cells..see my data below..using your formula..the rows 1 and 2 contradict..

Planned start date | New Task Completion Date | MS |

1/3/2013 | 1/3/2013 | 0 |

1/3/2013 | 1/4/2013 | 0 |

1/8/2013 | 1/8/2013 | 0 |

1/8/2013 | 1/8/2013 | 0 |

1/9/2013 | 1/9/2013 | 0 |

1/9/2013 | 1/9/2013 | 0 |

1/9/2013 | 1/11/2013 | 1 |

1/9/2013 | 1/9/2013 | 0 |

1/9/2013 | 1/10/2013 | 1 |

1/9/2013 | 1/9/2013 | 0 |

1/10/2013 | 1/11/2013 | 0 |

1/10/2013 | 1/11/2013 | 0 |

1/2/2013 | 1/2/2013 | 0 |

1/2/2013 | 1/8/2013 | 4 |

1/4/2013 | 1/4/2013 | -1 |

1/4/2013 | 1/4/2013 | -1 |

1/4/2013 | 1/4/2013 | -1 |

1/4/2013 | 1/7/2013 | 1 |

1/4/2013 | 1/4/2013 | -1 |

1/4/2013 | 1/4/2013 | -1 |

1/4/2013 | 1/4/2013 | -1 |

1/4/2013 | 1/8/2013 | 2 |

1/4/2013 | 1/4/2013 | -1 |

1/4/2013 | 1/4/2013 | -1 |

1/4/2013 | 1/4/2013 | -1 |

1/4/2013 | 1/7/2013 | 1 |

1/4/2013 | 1/4/2013 | -1 |

1/4/2013 | 1/4/2013 | -1 |

1/4/2013 | 1/4/2013 | -1 |

1/4/2013 | 1/8/2013 | 2 |

1/4/2013 | 1/4/2013 | -1 |

1/4/2013 | 1/7/2013 | 1 |

1/7/2013 | 1/8/2013 | 1 |

1/4/2013 | 1/4/2013 | -1 |

1/4/2013 | 1/4/2013 | -1 |

1/7/2013 | 1/7/2013 | 0 |

1/7/2013 | 1/9/2013 | 2 |

1/10/2013 | 1/10/2013 | 0 |

1/7/2013 | 1/7/2013 | 0 |

1/7/2013 | 1/7/2013 | 0 |

1/7/2013 | 1/7/2013 | 0 |

1/7/2013 | 1/8/2013 | 1 |

1/8/2013 | 1/9/2013 | 1 |

1/7/2013 | 1/7/2013 | 0 |

1/7/2013 | 1/7/2013 | 0 |

1/7/2013 | 1/8/2013 | 1 |

1/7/2013 | 1/7/2013 | 0 |

1/7/2013 | 1/7/2013 | 0 |

1/7/2013 | 1/8/2013 | 1 |

1/8/2013 | 1/8/2013 | 0 |

1/8/2013 | 1/8/2013 | 0 |

1/8/2013 | 1/9/2013 | 1 |

1/9/2013 | 1/16/2013 | 5 |

1/16/2013 | 1/22/2013 | 4 |

1/8/2013 | 1/8/2013 | 0 |

1/8/2013 | 1/8/2013 | 0 |

1/9/2013 | 1/9/2013 | 0 |

1/9/2013 | 1/10/2013 | 1 |

1/10/2013 | 1/10/2013 | 0 |

1/15/2013 | 1/15/2013 | 0 |

1/15/2013 | 1/16/2013 | 1 |

1/15/2013 | 1/16/2013 | 1 |

1/18/2013 | 2/19/2013 | 22 |

2/19/2013 | 2/19/2013 | 0 |

1/15/2013 | 1/15/2013 | 0 |

1/15/2013 | 1/16/2013 | 1 |

1/16/2013 | 1/18/2013 | 1 |

1/15/2013 | 1/15/2013 | 0 |

1/15/2013 | 1/16/2013 | 1 |

1/16/2013 | 1/18/2013 | 1 |

1/9/2013 | 1/9/2013 | 0 |

1/9/2013 | 1/9/2013 | 0 |

1/9/2013 | 1/10/2013 | 1 |

1/10/2013 | 1/10/2013 | 0 |

1/10/2013 | 1/10/2013 | 0 |

1/10/2013 | 1/10/2013 | 0 |

1/10/2013 | 1/10/2013 | 0 |

1/10/2013 | 1/10/2013 | 0 |

1/10/2013 | 1/11/2013 | 0 |

1/10/2013 | 1/10/2013 | 0 |

1/16/2013 | 1/29/2013 | 9 |

1/16/2013 | 1/29/2013 | 9 |

1/29/2013 | 2/1/2013 | 2 |

1/11/2013 | 1/11/2013 | -1 |

1/11/2013 | 1/11/2013 | -1 |

1/11/2013 | 1/11/2013 | -1 |

1/11/2013 | 1/14/2013 | 1 |

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Ok I think I understand the problem. JMP apparently does not follow the ISO 8601 standard (i.e first day of week is monday) but the US tradition of celebrating sunday as the first day of the week. This perfectly explains your results above as my formula assumes the former. However, it's trivial to adapt the formula to take that into account (or any other local definition of day of week or what days that are considered belonging to the weekend).

Try e.g. this:

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

Below is my test output and it appears to work. The "counter" halts over weekends (sat-sun where I live). Remove the -1 if you by "duration between two dates" mean to include the full duration of both start and end dates. (Here A = today, E = future days)

A | E | Weekday | Working days |

2013-04-05 | 2013-04-05 | Friday | 0 |

2013-04-05 | 2013-04-06 | Saturday | 0 |

2013-04-05 | 2013-04-07 | Sunday | 0 |

2013-04-05 | 2013-04-08 | Monday | 1 |

2013-04-05 | 2013-04-09 | Tuesday | 2 |

2013-04-05 | 2013-04-10 | Wednesday | 3 |

2013-04-05 | 2013-04-11 | Thursday | 4 |

2013-04-05 | 2013-04-12 | Friday | 5 |

2013-04-05 | 2013-04-13 | Saturday | 5 |

2013-04-05 | 2013-04-14 | Sunday | 5 |

2013-04-05 | 2013-04-15 | Monday | 6 |

2013-04-05 | 2013-04-16 | Tuesday | 7 |

2013-04-05 | 2013-04-17 | Wednesday | 8 |

2013-04-05 | 2013-04-18 | Thursday | 9 |

2013-04-05 | 2013-04-19 | Friday | 10 |

2013-04-05 | 2013-04-20 | Saturday | 10 |

2013-04-05 | 2014-04-21 | Monday | 271 |

2013-04-05 | 2015-04-22 | Wednesday | 533 |

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Calculating duration between two dates from different years

Yes it worked..thankyou so much!!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Calculating duration between two dates from different years

The data is Numeric COntinuous, Date format..and I want to calculate the number of weekdays excluding the weekends.. sample data:-

Planned start date | New Task Completion Date |

1/3/2013 | 1/3/2013 |

1/3/2013 | 1/4/2013 |

1/8/2013 | 1/8/2013 |

1/8/2013 | 1/8/2013 |

1/9/2013 | 1/9/2013 |

1/9/2013 | 1/9/2013 |

1/9/2013 | 1/11/2013 |

1/9/2013 | 1/9/2013 |

1/9/2013 | 1/10/2013 |

1/9/2013 | 1/9/2013 |

1/10/2013 | 1/11/2013 |

1/10/2013 | 1/11/2013 |