Excel Unplugged

Start And End Date Of The Week With Power Query

Start and End Of Week Dates With Power Query

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.

It’s amazingly simple

Start and end of week dates with Power Query

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

Start and end of week dates with Power Query

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 WeekStart and end of week dates with Power Query

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

Start and end of week dates with Power Query

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

Start and end of week dates with Power Query

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.

Start and end of week dates with Power Query

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

Start and end of week dates with Power Query

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!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: