One of the most popular posts on Excel Unplugged is Formula to get the Start and End Date of the Week. For the last two years I’ve been a strong advocate for Power Query. Now whereas Power Query is brilliant and can do amazing things, I like to point out the little things it does that just embellish Excel. So talking about things that Excel already does, but can be done better (faster) with Power Query. Recently, I wrote about “substituting” Vlookup’s with Power Query here and here. Now I will show you how to get the dates of the first and the last day of the Week with Power Query.

If you find it easier to learn through video, just click here or scroll to the end of this post to watch a video of getting the start and end of the week with an Excel formula, and I will also show you the Power Query solution. It explains how to use them correctly within your region. Back to the post.

It’s amazingly simple

Let’s start with a table of dates. Then we go to Data/Get & Transform Data and choose From Table/Range

This will fire up the Power Query Editor. Now let’s add a start and end date of the week.

The Start Date of the Week

We add that by simply selecting the column with dates, and we head on to Add Column/Date/Week/Start of Week

This gives us a new column with the start of week date. It’s as simple as that 🙂

End Date of the Week

You could probably guess this one :). We select the Date column again and this time we choose Add Column/Date/Week/End of Week

And there we have it

We now got the End of Week Date in a new column. All that is left for us to do is to Close & Load this query and load the data back to Excel.

And there it is… Twenty seconds later we got our Start of Week and End of Week Dates

Once again, Power Query just enhancing Excel. Beautiful!

I strongly suggest, you give Power Query a try and just check out the Add Column/Date options. Some will surprise and amaze you. And the ease of it is what is most amazing about it!

Bellow, you will find a video explaining how to change a date into the start or end of the week date.