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, typ