We will use this as part of our formula to create our data set and graph for the previous weeks. Creating an options menu for selected weeks Set up the menu dataīelow the This Week title, we will create a list of options for our users to select a previous week. Your row widths for your days are a bit wide so go ahead and reduce the column widths a little. Then on cell F8 add the text, This Week in bold. Starting in cell G7 add the abbreviated days of the week from Sunday across each column to Saturday. Now that we have all the data we need, we can now set up all the headers and sidebar titles for our comparison. You can find another example of WEEKDAY in use below:Ĭount the Number of Selected Days of the Week in Google Sheets Setting up the comparison data Then drag the formula all the way down to the bottom to duplicate it. In the next row add your WEEKDAY function and reference cell A2. In column D, create a header in the first row called Day of Week. This will mean that our start of the week, Sunday, will be displayed as 1. You might expect to see a text returned, like, SUN, MON or something, but WEEKDAY will return just a number from 1 to 7.įor our example, we will just use the first argument. This function takes one main argument, the date and an optional number argument that determines what day the week starts on. We can accomplish this by using the Google Sheets WEEKDAY function. Our next step is to get the day of the week for each date. In the column to the right of the Sales data (column C) create a new header called Week of Year and then add your WEEKNUM function and reference the first cell of date data on the list.ĭrag down the formula in C2 all the way down the column. Take a look at the example below:īack to our Sales Data example. However, for us, we just need the first one. We can also change which day the week starts with an optional second argument. The WEEKNUM function takes a date as a mandatory argument. We can do this easily in Google Sheets with the WEEKNUM function. For example, the first week of the year would be week 1, the second, week 2, etc. Our first task is to determine which data is on what week for us to then chart and graph. Oh…and don’t worry about the dates, I’ve set it up for you so that the dates will always have a set of data one month previous to your current week even if it is five years from now. Just go to File > Make a copy so you have your very own version. Here is a link to the raw data we will be using so you can play along: Well keep our data and the end result above in the same sheet tab as the data source for convenience, but you can put your comparison data anywhere even in another Google Sheet! I’ve titled this spreadsheet creatively, Sales Data. Check out the sample below: Click to Expand!įrom this dataset, we want to find the current week’s set of data right up to the current day and then compare it to: This example starts off with a list of dates and corresponding sales data for each date. The Example – Compare the Current Week’s Data with Previous Week’s Data
Finding the comparison data for the selected week.
Creating an options menu for selected weeks.