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.
Best Answer
-
@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
Answers
-
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.
-
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
-
@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
-
@Eric Law Thank you! This is perfect!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 405 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!