• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 62
  • Last Modified:

Finding earliest date in table range with multiple non-contiguous date columns while excluding blanks and zeros

I am trying to find a formula that returns the earliest date from a table called DC_Table that has 11 non-contiguous 'Date' columns, excluding blank cells or cells = 0.

All cells in the DC_Table are linked cells, and for the purposes of providing an spreadsheet example I have added the source spreadsheet to the workbook. It's called DC_WOs_Extracted.

There are several non-continuous columns in DC_Table that are date fields [Date1], [Date2], etc., and the alternating columns are 'Minutes' fields [Min1], [Min2], etc.

As shown on the worksheet tab called  'Dates', the following formula works but seems cumbersome. It references the first 3 [Date] columns (ultimately I will need to include columns Date4 through to Date11), and returns the earliest date found:

=IF(MIN(DC_Table[[Date1]:[Date1]],DC_Table[[Date2]:[Date2]],DC_Table[[Date3]:[Date3]])<>0,MIN(DC_Table[[Date1]:[Date1]],DC_Table[[Date2]:[Date2]],DC_Table[[Date3]:[Date3]]),"")

I am hoping for a formula that is simpler (ie., using the range as opposed to having to list each date column as above), as some of my spreadsheets will have up to 25 [Date] columns. Is that possible?

Thanks,
Andrea
Metrics_DCWOs_01FEB2018_EE.xlsx
0
Andreamary
Asked:
Andreamary
1 Solution
 
ShumsDistinguished Expert - 2017Commented:
Hi Andrea,

Better to create a Named Range for non-contiguous columns, in your case, I created a Named Range called, DateCols with below formula:
=DC_Table[Date1],DC_Table[Date2],DC_Table[Date3],DC_Table[Date4],DC_Table[Date5],DC_Table[Date6],DC_Table[Date7],DC_Table[Date8],DC_Table[Date9],DC_Table[Date10],DC_Table[Date11]
Then for Earliest Dates enter below formula:
=IF(NOT(ISBLANK((DateCols))),SMALL((DateCols),1))

Open in new window


And for Latest Dates enter below formula:
=IF(NOT(ISBLANK((DateCols))),LARGE((DateCols),1))

Open in new window

Whenever you have new date columns, just add in Named Range.
AndreaMary_Metrics_DCWOs_01FEB2018_.xlsx
1
 
AndreamaryAuthor Commented:
I hadn't realized that a range could include non-contiguous columns - this is great - thanks so much, Shums! :-)

Cheers,
Andrea
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now