How to Find Week Start Date and End Date in Google Sheets with Formula

0
522
Week Start Date and Week End Date

Here I’m going to share you the formula to find Week Start Date as well as Week End Date in Google Sheets. I’ve already covered all the Google Sheets Date based functions in a separate post.

Lots of spreadsheet calculations are based on dates and also time. Many occasions as part of your job, you may want to find and use Week Start Date, Week End Date or both.

For example, when you want to filter, import data from another sheets using IMPORTRANGE, or Query a data set based on last 1 week, 2 weeks, 3 weeks or any number of weeks, you may want to find the week start or week end dates.

You can find Week Start Date or Week End Date from today’s date or any other given dates in Google Doc Spreadsheets. We are going to check all the aspects related to this.

Formula to Find Week Start Date in Google Sheets

Here in this example, we are going to use two of the Google Sheets Date functions. You may please refer our Google Sheets FUNCTIONS guide to pick the function you want to learn.

First I’m going to find the Week Start Date based on today’ date. Today’s date as per my system in “dd/mm/yyyy” format is as below.

Today’s Date: 16/11/2017 that is Thursday, 16 November 2017.

Find Week Start Date Based on Today’s Date

Week Starts From Monday – Sunday.

Here I am considering week starts from Monday to Sunday. We considered today’s date as 16/11/2017. So the following formula would return the date 13/11/2017, i.e., Monday, 13 November 2017 as week start date.

=TODAY()-WEEKDAY(TODAY(),2)+1

Week Starts From Sunday – Monday.

TODAY()-WEEKDAY(TODAY())+1

This formula would return the date 12/11/2017, i.e., Sunday, 12 November 2017 as week start date.

Find Week Start Date Based on Any Given Date

Week Starts From Monday – Sunday.

Here is the formula to find week start date based on a given input. Here our input date is 15/12/2017, i.e., Friday, 15 December 2017. You just need to change the “today()” part of the above formula.

=date(2017,12,15)-WEEKDAY(date(2017,12,15),2)+1

Week Starts From Sunday – Monday.

=date(2017,12,15)-WEEKDAY(date(2017,12,15))+1

You only need to change the blue coloured dates to find week start date of any dates.

Formula to Find Week End Date in Google Sheets

Here things are pretty cool. Just add 6 to any of the above formulas to get the week end date of that given week.

Example: Here I am picking the very first formula in this example and adding 6 to the end part of the formula.

Original Formula to find the week start date.

=TODAY()WEEKDAY(TODAY(),2)+1

It returns the date Monday, 13 November 2017. Now see the below formula.

=TODAY()WEEKDAY(TODAY(),2)+1+6

This formula returns the week end date Sunday, 19 November 2017

That’s all.

LEAVE A REPLY

Please enter your comment!
Please enter your name here