All Dates from Duration in a dropdown cell

Hi All, I am looking for a formula that take the data from the start and end dates columns and lists all in that duration in the "Dates" column.

I am running 4 conference rooms within the center and I need to count the unique days that the aggregate center is used.

For example:

Conference Room 1 is active Jan 1 - Jan 3 = 3 Days

Conference Room 2 is active Jan 2 - Jan 4 = 3 Days

Aggregate Center Active Days = 4 Days

Jan 1, Jan 2, Jan 3, Jan 4

Thank you

Later on I am running a metrics sheet to count all unique dates, so I know how active my conference center is.

Find more posts tagged with

Accepted answers

Eric Law

@Gweiss please use an @ to communicate in posts as the person you are talking to does not get notifications if you respond otherwise. If you want something like what you have, you will also need a separate sheet with the list of dates to pull from. I put all mine in one sheet as I am not building something with this and am just testing. When you have the other sheet, you can replace Date:Date with the range of the dates you want to pull.

Here is the aggregate formula that I used with my snips. =JOIN(COLLECT(Date:Date, Date:Date, >=Start@row, Date:Date, <=End@row), ", ")

Good luck

All comments

Eric Law

Hello @Gweiss I don't think you need that helper column with what you are trying to do. I am assuming that you are using a separate sheet to aggregate how many meetings where within those days, using a sheet with all 365 days?

What you can do is use a COUNTIFS statement with the Start Date and just use the parameter to be greater than or equal to the start date and less than or equal to the end date.

Gweiss

Hi Eric, thank you for the suggestion,

Unfortunately, I am running 4 conference rooms within the center and I need to count the unique days that the aggregate center is used.

For example:

Conference Room 1 is active Jan 1 - Jan 3 = 3 Days

Conference Room 2 is active Jan 2 - Jan 4 = 3 Days

Aggregate Center Active Days = 4 Days

Jan 1, Jan 2, Jan 3, Jan 4

Thank you

Eric Law

Here is the aggregate formula that I used with my snips. =JOIN(COLLECT(Date:Date, Date:Date, >=Start@row, Date:Date, <=End@row), ", ")

Good luck

Gweiss

@Eric Law Thank you! This is perfect!

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.