All Dates from Duration in a dropdown cell

Gweiss
Gweiss ✭✭
edited 11/27/23 in Formulas and Functions

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

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    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

  • Eric Law
    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
    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
    Eric Law ✭✭✭✭✭✭
    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

  • Gweiss
    Gweiss ✭✭

    @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!