Dynamic Formula Change to Date Function

Hello, I have a list of "Created Dates" and I'm trying to pull the appropriate "Created Date" into each Week and under its Day of the Week in a more efficient way.

So far I have used "=COUNTIF([Created Date]:[Created Date], =DATE(2020, 3, 1))" to find 1 item for the Week of 3/1/2020 on Sunday specifically. As well as using "=COUNTIFS([Created Date]:[Created Date], <=DATE(2020, 3, 7), [Created Date]:[Created Date], >=DATE(2020, 3, 1))" total the Week of 3/1/2020.

My difficulty is in trying to fill each cell in this table, this would be a very manual task. Especially since the sheet is expected to grow each week. My goal is to try and find a way to have the formula "adjust" so that it could be more easily changed and updated, rather than rewriting rows and rows of Formulas for each week.

Hoping there is an answer to this or some other method I may not be aware of.

Thank you in advance.


Tags:

Best Answer

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Answer ✓

    So you want to get the number of rows that were created for each day of the week?

    What I've done is automated it all by making Sunday-Saturday columns with an Adder row that changes the date based on week start. So Monday is adder of 1 since it is 1 day after week start.

    These values are used in subsequent rows to count how many times the proper date appears in the Created row. I made up created values but it will work the same in practice. The formula in each of the cells is similar:

    In the Sunday Column: =COUNTIF($Created:$Created, $[Week Start]@row + VALUE(Sunday$1))

    In the Monday Column: =COUNTIF($Created:$Created, $[Week Start]@row + VALUE(Monday$1))

    And so on...

    Here is a screenshot

    There are many 0 values so I added conditional formatting to grey these out on a per row basis.


    I published the Sheet, you can see it here: https://app.smartsheet.com/b/publish?EQBCT=3917f0c75ec14336ab03e468886a4972

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Answer ✓

    So you want to get the number of rows that were created for each day of the week?

    What I've done is automated it all by making Sunday-Saturday columns with an Adder row that changes the date based on week start. So Monday is adder of 1 since it is 1 day after week start.

    These values are used in subsequent rows to count how many times the proper date appears in the Created row. I made up created values but it will work the same in practice. The formula in each of the cells is similar:

    In the Sunday Column: =COUNTIF($Created:$Created, $[Week Start]@row + VALUE(Sunday$1))

    In the Monday Column: =COUNTIF($Created:$Created, $[Week Start]@row + VALUE(Monday$1))

    And so on...

    Here is a screenshot

    There are many 0 values so I added conditional formatting to grey these out on a per row basis.


    I published the Sheet, you can see it here: https://app.smartsheet.com/b/publish?EQBCT=3917f0c75ec14336ab03e468886a4972

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

  • Hey Dan,

    Thank you for your assistance with this, it has been immensely helpful. Your solution has worked beautifully for the table. I even applied the conditional formatting as you suggested.

    I tried to edit the formula to total the number of Created Dates for each Row. This has been unsuccessful on my part. I was attempting to have the formula in a Column called "Weekly Total" and be as Dynamic/Flexible as the one you provided. It's great that I can now just drag the formula down to add new weeks to this.

    Ideally I wanted the "Weekly Total" Column to search the "Created Dates" Column and add the Dates for a Week. So an example would be to parse the "Created Dates" Column for 3/1/20-3/7/20 and pull the total number of matches for all the Dates in that range. Is something like this possible or would a SUM formula in the Column be more ideal to total each Weekly Row?

    Thank you again in advance for anymore insight on this,

    Lawrence

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    edited 07/21/20

    @Lawrence Blanco I am glad this was helpful for you.

    To get the weekly total I'd suggest not changing the existing formulas, but rather, adding the below formula in the "Weekly Total" column:

    =SUM(Sunday@row:Saturday@row)

    I've added this to the example sheet, accessible at https://app.smartsheet.com/b/publish?EQBCT=3917f0c75ec14336ab03e468886a4972.

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

  • Thank you for all the help Dan! It worked perfectly!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!