Start And End Date Of The Week 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.
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.
Related Posts
- February 24, 2015
Surprisingly, this is not a post about the Translate feature in ...
- May 19, 2014
We have a column of dates in Excel, and our goal is to get Q1, ...
- April 16, 2013
Here is our “problem”. When you create a Pivot Table ...
Comments 4
Leave a Reply
Excel Unplugged acknowledgements
Donate
Great post!
I found power query very useful indeed. However, I came across excel limitation related to the number of records that could be managed. I have to unpivot several files that are in matrix format to a slim and tidy one.
I need to prepare a file with macro that could be run by my colleagues by which they could be able to unpivot all the files stored in a defined folder. They should open one file and run the macro. I have recorded a pilot macro that stores the data in the same file that runs it (because it looks like is not possible to save and load on a actual separated workbook). Then I am trying to tweak a loop macro that should open target file, do the job and close it. The open the second file and do the same, then the third and so on until all the files in the folder are unpivoted. The issue is that I have not the proficiency in VBA to be able to get right the multiple connections between the objects and the function called.
To summarise, I need to have this file that run the macro being able to: 1) open the files in the folder 2) convert each file separately 3) save it in the specified folder with the name of the original file 4) close the unpivoted file 5) repeat this for all the files in the folder.
If you want to help me I can send you the code so far used.
Many thanks
Hi Vito. I’ll be honest. This sounds very interesting. Not sure what you mean by the limited number of records. If you push it into Power Pivot, there could be millions… But nevertheless…
I always appreciate this kind of questions but in the uniqueness of the current situation, I simply can’t give it the time needed. Sorry for that and best of luck with the project.
This is a great tip, but the End of Week calc doesn’t actually seem to work. If you look at the dates being generated, they are all Sundays. So are the Start of Week dates returned (which are correct). Shouldn’t End of Week be returning Saturday if the Start of Week is Sunday?
Depends on your regional settings Paul. The US regional settings will take Saturday as the last day of the week. This video explains it best.