Collect and display nearest dates

I have a job tracker designed to track the art components of our jobs. We would like to have a column display the next nearest due date for that row. My trouble is we may have 1-10 due dates on a given row and there are non-date columns between each of the due date columns. I'm fairly sure I need to use a MIN(COLLECT( type formula, but not sure how to arrange it. Screen shot below w/ pertinent columns circled.

Find more posts tagged with

Formulas

Marketing

IT & Operations

Comments

Paul Newcome

Try something like this...

=MIN(COLLECT([First Date Column]@row:[Last Date Column]@row, [First Date Column]@row:[Last Date Column]@row, AND(ISDATE(@cell), @cell > TODAY())))

Elliot Holder

So I tried that and got and "Invalid Column Value". I may have typed in something incorrectly. Formula below.

=MIN(COLLECT([Assets Due]@row:[Component10 Due to Deliver]@row, [Assets Due]@row:[Component10 Due to Deliver]@row, AND(ISDATE(@cell), @cell > TODAY())))

Paul Newcome

Is your [Next Due Date] column set as a date type?

MakeItHappen

Exactly what I wanted. It worked for me.

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.