Week Of ... : How do I get the starting Monday (or Sunday) for a given day ?

-

 


There is a fairly common scenario where we need report weekly progress on events : it could be weekly sales or weekly registrations or audience growth, etc. 

Your spreadsheet typically contains daily numbers Imagine you have events (eg "orders") on a given date but you want to aggregate those events by week. 

We could use the weeknum() function which will return a number between 1 and 52, but it's not super intuitive (when exactly is week number 27?). 

A more intuitive and common option is aggregate daily events into "week starting" dates and there's an easy formula for that : 

With your source date on cell A2 : 

=A2-WEEKDAY(A2,3) (week starting Monday)

=A2-(WEEKDAY(A2,1)-1) (week starting Sunday)


How does this work ?

Most spreadsheets allow you to add / subtract any number of days to / from a date to obtain a new date. Try this in the formula bar :  ="25/1/2021" + 1.  The end result should be 26/1/2021. 

The weekday(date, type) function gives you a "date id", 1 to 7 or 0 to 6 depending on the type (a value between 1 and 3).  Knowing the day id helps you make a simple calculation to find the first day of the week.


Weeks starting on Monday
If you set the type to 3 on your weekday() function, then Monday is the first day of the week and its id is 0. The calculation is then super simple (date in non-US format) :

  • Tuesday Feb 9, 2021 is day id 1, if I type in the formula bar "9/02/2021" - 1  =  Mon, Feb 8 2021
  • Thursday Feb 18, 2021 is day id 3, "18/02/2021" - 3 = Mon, Feb 15 2021
  • Automatically : =A2 - weekday(A2,3) with A2 being 18 Feb 2021.

Weeks starting on Sunday (US-style)

In order to have weeks starting on Sundays, you need to set the type of the weekday() to 1. 

This is the default value, so you can actually skip adding the type entirely, but because we are at day id 1, we need to subtract -1 from our calculation. 

Example
This is Thursday, February 4, Thursday is day id 5. In order to get to Sunday, we need to subtract 4, in other words the current day id minus 1. 

  • cell A2 = Thursday Feb 18, 2021 
  • Thursday is a US Day # 5
  • Sunday 31st Jan =A2 - (weekday(A2,1) - 1)   or simply 
  • Sunday 31st Jan =A2 - (weekday(A2) - 1)  because type=1 is the default value


Have fun with spreadsheets !




Comments

Post a Comment

Popular posts from this blog

How to calculate the quarter for a given date in Google Sheets

-
Image
Quarterly reporting is important for many businesses. While most spreadsheets can extract the day, date, month or year from a date with dedicated functions, none of them have  a built-in quarter() function .  In this article we will show you how you can easily get the quarter of the year from a date. 1. The month() function The month() function in Google Sheets gives you the month number from a date: if your cell A2 contains Sep-30 2017 or 9/30/2017, month(A2) will show "9". 2. A little math There are four quarters in a year with three months each. Month 1 is in Q1, month 5 is in Q2, month 6 is also in Q2, while month 7 is in Q3. The logic is simple enough that we want to automate this with some math. Here is the trick : if we know the month and divide the month by 3, we are almost there : We can see that March, June and December are in the right quarter number but for other months, the floating numbers are cause for concern. Well, not for long. 3. Rounding nu
Read more

How to sort pivot data in descending order in Google Sheets (Part 1)

-
Image
One of the main benefits of working with spreadsheets is the ability to aggregate data, that means to sum up values, count a number of items, etc. Google Sheets has an easy built-in  Pivot Table.  If you are new to spreadsheets, start there. If you are looking for an even more powerful solution, one that helps you have multiple pivot tables on one sheet OR one that allows you to show in which order you want to sort your numbers (ascending, descending), then the  Query() function  is what you need. 1. Pivot Tables In the sheet above, the world's population is listed by country and column D holds the country's region. In the example below, we want to aggregate the world's populations by region. Click on Data, then Pivot Table report... In the Report Editor, the field "Region" is added to the Rows and the field "Population" (which holds the population count from each country) is added to the Values section. You can see that numbers are not
Read more

How to sort pivot data in descending order in Google Sheets (Part 2)

-
Image
In Part 1 , we saw that Pivot table was the classic spreadsheet way of aggregating and displaying numbers from many rows and columns of data. We also saw that sorting the way we wanted is not always possible. Using the query() function is one way of doing that and it opens up many other possibilities. 2. the Query() function The Query() function can easily fix our sorting problem, but it requires some basic knowledge of SQL, a special language used to extract and order data from databases. Fortunately, the syntax used in Spreadsheets is simplified : The Query() is composed of 3 parameters : =query( source of data ,  the query itself ,  the column headers if required ). Here is an example and its result below : =query(facts!A1:D50,"SELECT D, sum(C) group by D order by sum(C) desc",1) This query gets two columns, Region (column B) and the sum of Population (column C) from the sheet called "facts". It then orders the table by decreasing order of Population
Read more