Excel - Three Ways to Count By Week - Episode 2589

Microsoft Excel tutorial on how to count records by week.

Welcome to another episode of MrExcel's netcast, where we tackle your Excel questions and provide helpful solutions. In today's episode, we will be discussing how to count by week in Excel. This question was brought to our attention by Moe Sadr, who was looking for a way to count records by week instead of by month. So, let's dive in and explore three different methods to achieve this task.

The first method we will be discussing is using a pivot table. We have a list of daily dates and we want to roll them up to weekly counts. To do this, we will insert a pivot table and place it in an existing worksheet. Then, we will add the date field to both the rows and values section. Next, we will go into the group field and unselect months, choosing only days. This will enable us to use the spin button to roll the dates up to seven-day periods. However, we need to be careful as the pivot table will start on the first day, which may not align with the start of the week. We can adjust this by selecting the previous Monday as the starting date. This method is great for quickly summarizing data, but there are other ways to achieve the same result.

The second method involves using the WEEKDAY function. This function returns a number from zero to six, representing the day of the week. We can use this to our advantage by subtracting the weekday from the date, which will give us the previous Monday. We can then use the fill handle to copy this formula down and get the week starting date for each record. To get a list of unique week starting dates, we can use the SORT and UNIQUE functions. Then, we can use a COUNTIF formula to count the number of records for each week. However, we may run into some trouble with the ISOWEEKNUM function, which assigns a number to each week of the year. This may not align with our desired week starting dates, so we will need to adjust the formula to include the year as well.

The third and final method we will be discussing is using the ISOWEEKNUM function directly. This function returns a number from one to 53, representing the week of the year. We can use this in a similar way to the WEEKDAY function, but we will need to adjust the formula to include the year. This method may be more straightforward for some users, but it may also require some additional adjustments to get the desired result.

In conclusion, there are multiple ways to count by week in Excel, and it ultimately depends on your specific data and preferences. Whether you choose to use a pivot table, the WEEKDAY function, or the ISOWEEKNUM function, the end result will be the same. Thank you to Moe for sending in this question and thank you for tuning in to another episode of MrExcel's netcast. If you found this video helpful, please consider liking, subscribing, and ringing the bell to be notified of future episodes. And as always, feel free to leave any questions or comments down below. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

This video includes three methods:
Using Group Field in a pivot table to roll up to weeks.
Using a formula of Data minus WEEKNUM(,3) to back the date to Monday.
Using ISOWEEKNUM plus the year along with SORT, UNIQUE, and COUNTIF.

To download the data, visit: https://www.mrexcel.com/youtube/GLkEsvirk9Y/

Table of Contents
(0:00) Count by Week in Excel pivot table
(0:43) Pivot convert dates to weeks
(1:33) Using date minus WEEKDAY(,3)
(2:19) Sort, Unique, COUNTIF
(2:53) Using ISOWEEKNUM in Excel
(3:30) Excel formula for Year and Week Number
(3:50) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
how to group weeks in excel pivot
how to group dates into weeks excel pivot table
how to do weeknum in excel
how to get weeknumber for a date in excel
how to count week number in excel
how to use the weeknum function in excel
how to calculate the week number in excel
how to get week number in excel from date

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1235305/

Видео Excel - Three Ways to Count By Week - Episode 2589 канала MrExcel.com