Need some formula assistance

Hello! I am usually pretty savvy at figuring out formulas, but I am truly stuck on solving this one. Does anyone know a way to solve for the below situation? I feel like it's doable and likely easier than the way I'm trying to approach it, but cannot get the right combination.

Background:

Our project plans/schedules have multiple different dates and classifications for those dates. There are 4 classifications for those dates and a date classification can appear up to 6 times, all different dates, all different ranges. I need to display the first date closest to today with a specific classification. Then, what I need is Smartsheet look for the next date once either the finish date is reached or the task is marked complete from that original date classification, so and so forth. It would need to pull this single date in to a single cell in the profile data section of the grid, then pass it to the metadata sheet. The classification column is set to be multi-select, but the classification will only appear once.

I have the metadata sheet and profile data portion setup. What I need is to find the start date closest to today and display it. Then once that finish date is reached, display the next date with that same classification. Essentially, I need this single field to be updated dynamically to accommodate a countdown.

The struggle that I'm having is that all dates are in the future, so a less than or equal to won't work. And if I use greater than or equal to, I fear it will confuse the formula and cause an error.

I am not married to classifying these dates with 6 different types per classification. Whatever gets to the end goal of constantly displaying the next date for the classification would be fine.

Example data (this is not the real data, just a fun play on it while I test this out)

Date Classifications in this example below are "Fun Date" and "Beach Date". In our real-time sheet, the start and end dates may or may not overlap. Thanks in advance. (please and thank you :))

Find more posts tagged with

Formulas

Accepted answers

Adrien Leduc

I would first recommend that you add a first row on top of all the Fun Dates and another on top of all the Beach Dates, and so on, and make them their parents using indentation. After having added these row, make this kind of formula:

=MIN(COLLECT(CHILDREN(),CHILDREN(), >=TODAY()))

You can add more criterias as well, for example if you have a status column:

=MIN(COLLECT(CHILDREN(),CHILDREN(), >=TODAY(),CHILDREN(Status@row),<>"Complete"))

Paul Newcome

If you are marking statues, I would use something like this:

=MIN(COLLECT(Start:Start, [Date Classification]:[Date Classification], CONTAINS("Fun", @cell), Status:Status, OR(@cell = "Not Started", @cell = "In Progress")))

All comments

Adrien Leduc

=MIN(COLLECT(CHILDREN(),CHILDREN(), >=TODAY()))

You can add more criterias as well, for example if you have a status column:

=MIN(COLLECT(CHILDREN(),CHILDREN(), >=TODAY(),CHILDREN(Status@row),<>"Complete"))

Paul Newcome

What would the final output look like? Can you provide a screenshot of the data manually mocked up to show that?

ebeall

Hi Paul,

Whoops! I should have included that, my bad! Please see below; I should have also noted that these are project enabled sheets.

ebeall

Hi Adrien,

Thank you for your response!

I have something similar to your second suggestion currently, but it isn't doing quite what I need. So perhaps I need to stare at it a little bit more and make sure I've got the criteria right.

ebeall

I should add the expected outcome, and that these are project enabled sheets:

Paul Newcome

If you are marking statues, I would use something like this:

=MIN(COLLECT(Start:Start, [Date Classification]:[Date Classification], CONTAINS("Fun", @cell), Status:Status, OR(@cell = "Not Started", @cell = "In Progress")))

Adrien Leduc

Hi again,

So looking at the screenshot and with additional info, I would put the formulas on what looks like Description row 5 and 12 as formulas won't work on Start and End columns.

=MIN(COLLECT(CHILDREN(Start@row),CHILDREN(Start@row), >=TODAY(),CHILDREN(Status@row),<>"Completed",CHILDREN(Status@row),<>""))

Then on Description 2 and 3 would do = Description5 and =Description12

If there is an issue, please share the a screenshot with the error message.

ebeall

Hi @Paul Newcome and @Adrien Leduc -

Thank you both for your assistance! I was completely overthinking it. Adrien's solution would work, but since I need it in the summary section of the sheet (control center purposes-I realize now in the initial post I did not make this completely clear, my apologies), I ultimately went with:

=IFERROR(DATEONLY(MIN(COLLECT(Start:Start, [Date Classification]:[Date Classification], CONTAINS("Beach ", @cell ), Start:Start, Start@row >= TODAY(), Status:Status, Status@row <> "Complete"))), "Not scheduled") + ""

FYI to future searchers/readers - Adding the + "" at the end (due to the type of column it is in) would prevent a data type error and displays the date. DATEONLY was used because for some odd reason, it kept showing a time even though there is no time recorded in the sheet, anywhere. And the column the formula is in is Text/Number.

Thank you both very, very much. I appreciate your assistance!!

Paul Newcome

The DATEONLY is required because dependencies are turned on. When those are turned on in a sheet, there is also a time stamp stored on the back-end of the start and end date columns.

I must have misunderstood your needs. When you said

"look for the next date once either the finish date is reached or the task is marked complete"

I took that to mean that if there was a row that was "Active" because it hasn't been marked as completed and the end date is still in the future, but the start date is in the past, you would want to pull in that start date. That's why I left the Start date being in the future out of my suggestion.

Using the Start date being in the future means that on 31 March, the output for the next fun date will be 11 May even though the row for 30 March's fun date does not have a finish date in the past and may not be marked as "Complete".

ebeall

No problem, thank you!! It helped tremendously, either way. When I got back to looking through it, and the recommendations, I realized I had completely over thought it.

Thank you very much, again!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.