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
AndreamaryAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsExcel & VBA ExpertCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndreamaryAuthor Commented:
I hadn't realized that a range could include non-contiguous columns - this is great - thanks so much, Shums! :-)

Cheers,
Andrea
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.