Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- 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
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

find duration from a table of dates

Created:
Oct 9, 2020 2:02 PM
| Last Modified: Oct 11, 2020 9:49 AM
(300 views)

#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

Highlighted
##

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

Re: find duration from a table of dates

There is not an attached data table

Jim

Highlighted
##

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

Re: find duration from a table of dates

hello @txnelson i have uploaded the data table

Highlighted
##

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

Re: find duration from a table of dates

Created:
Oct 11, 2020 10:02 AM
| Last Modified: Oct 11, 2020 10:05 AM
(225 views)
| Posted in reply to message from tonkatsu2020 10-11-2020

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

-Jeff

Highlighted
##

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

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?

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

Highlighted
##

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

Re: find duration from a table of dates

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