Dynamic Formula Change to Date Function

Lawrence Blanco

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.

Screen Shot 2020-07-17 at 3.41.37 PM.png

SoS | Dan Palenchar

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

SoS | Dan Palenchar

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

Lawrence Blanco

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

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

Lawrence Blanco

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