# All Dates from Duration in a dropdown cell

Options
✭✭
edited 11/27/23

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.

• ✭✭✭✭✭✭
Options

@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

• ✭✭✭✭✭✭
Options

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.

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

@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

• ✭✭
Options

@Eric Law Thank you! This is perfect!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!