• 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:


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?

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:
Then for Earliest Dates enter below formula:

Open in new window

And for Latest Dates enter below formula:

Open in new window

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

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