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
tonkatsu2020
Level II

find duration from a table of dates

#i have this attached table where the dates are submitted at the end of 12 different production steps (here column 1 to column 12). each row represent a unique production object. so i have 27 prod objects, each going through 12 prod steps; and hence have 12 prod dates. #I need to find the following: the duration required for the prod object to go from step1 or some other step to last completed step. Depending on the object: the last completed date can be step 12 or anything between step 2 and step 12. same goes for the start date, it does not necessarily start from step 1, so it can start from any step like 8 or 10 and then end at step 12. THE END DATE IS ALWAYS LATER THAN THE START DATE. # i need to write a function that would find the duration for all production objects/rows whether the first step is (1 to 12 ) and the last completed step is (1 to12) . please note the last completed date is not necessarily current/today date. it can be any date in the past/present/future. i am not sure how to script this write this function.

5 REPLIES 5
txnelson
Super User

Re: find duration from a table of dates

There is not an attached data table
Jim
tonkatsu2020
Level II

Re: find duration from a table of dates

hello @txnelson i have uploaded the data table
Jeff_Perkinson
Community Manager Community Manager

Re: find duration from a table of dates

Thanks, that helps.

 

The Minimum() and Maximum() functions will work for you here.

 

2020-10-11_12-56-41.274.png

 

NB: In the Formula Editor you can click on Column 1 and shift-click on column 12 and drag them into the argument for Minimum() and Maximum().

FormulaEditorDrag.gif

-Jeff
tonkatsu2020
Level II

Re: find duration from a table of dates

#Jeff, thank you for your solution. I should have also added that I have to implement this in a larger data table where the productions-steps/columns are not fixed; i.e. the data will be scattered across aprox <200 columns. Also the production-objects/rows will be increasing every day, so not limited to 27 rows here. It will be 100+ rows everyday.
#Hence in order to put these into a script: 1) how do i reference the column ranges where the column qty is not fixed. 2) how do i reference the specific columns that indicate the START and END dates?
ThuongLe
Level IV

Re: find duration from a table of dates

Jeff's answer is independent of row quantity. So no worry
Thuong Le