topic Re: find duration from a table of dates in Discussions

find duration from a table of dates

tonkatsu2020 — Sat, 10 Jun 2023 23:20:08 GMT

#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~~.

Re: find duration from a table of dates

txnelson — Fri, 09 Oct 2020 22:57:07 GMT

There is not an attached data table

Re: find duration from a table of dates

tonkatsu2020 — Sun, 11 Oct 2020 16:45:23 GMT

hello @txnelson i have uploaded the data table

Re: find duration from a table of dates

Jeff_Perkinson — Sun, 11 Oct 2020 17:05:08 GMT

Thanks, that helps.

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

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().

Re: find duration from a table of dates

tonkatsu2020 — Sun, 11 Oct 2020 23:47:16 GMT

#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?

Re: find duration from a table of dates

ThuongLe — Mon, 12 Oct 2020 04:20:47 GMT

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