What is the dynamic date formula to update based off a week worth of data?
I'm creating a dashboard for my department using a calculation sheet that pulls data from another sheet. While I've managed to come up with the basic calculations I need, I'm not sure how to make them dynamic so the dashboard displays data for the week (Sunday  Saturday). I don't want to have to manually change things from week to week if possible. Mind you, I'm not that great with formulas.
I'm using the following formula for once of my totals which refers back to a data sheet. In that same sheet, I have a created date column. What should the formula be?
=COUNTIF({Gate Checkin Coke Range}, 1)
Answers

I created a week # column that calculates the number of the week. Can I use that column in my formula somehow.

Hi @Chris Edwards,
Yes, you can. You would amend your formula to COUNTIFS. If you were looking to get the data for the previous week (as an example):
=COUNTIFS({Gate Checkin Coke Range},1,{Week Number},WEEKNUMBER(TODAY(1)1))
You can use a similar function to mean you could skip the Week helper column, but for some reason I've only been able to get it to work properly with SUMIFS, not COUNTIFS, so whether this would be any use to you would depend on whether you have any numerical value to SUM or not (though you could obviously have a helper column to do this!).
Hope this helps, but if you've any problems/questions then just post! 🙂

That worked! Thank you so much.
What if I want to create another metric that would calculate sum based on the day of the week using one of the two columns above (week# or created)?
Then I can have the sum for each day of the week that will update my dashboard.

Yes, you can use both. For example, for today (Tuesday):
=SUMIFS({Column to sum},{Created},WEEKDAY{@cell}=3,{Week Number},@cell=WEEKNUMBER(TODAY(1)))
To change the day, alter the number after WEEKDAY (Sunday =1, Saturday = 7).

Nick,
What is @cell?

When you are using a column reference, you use it to check a cell's contents for the specified criteria.
Looking at the above formula, I've messed up the brackets around the WEEKDAY portion of the formula  these should be rounded ( ), not { }.
Help Article Resources
Categories
Check out the Formula Handbook template!