You can create complex functions with Excel to handle dates. But it makes more sense to get your data structured correctly and then you can use simpler date formulas. Power Query allows you to fix your data so that you can use those simpler formulas.
Power Query is available for Excel 2010 and later versions. Power Query is an add-in to Excel 2010 and 2013 and is included in the Data ribbon in Excel 2016 (called Get & Transform). To see how you can download Power Query if you don’t have it installed check out this previous blog post.
Power Query is also available in Power BI. Power BI is a separate application to Excel. Any skills you learn in Power Query in Excel are directly transferable to Power BI.
I added another way to do this at the bottom of the post – hat tip to Wyn Hopkins for suggesting it.
Look at the data shown in the screen shot below. This is the Power Query window, a separate window to Excel.
The Date column holds daily sales. In doing some calculations it can be easier to have a column that displays the month. This can be achieved easily in Power Query by simply duplicating the column and then transforming it.
To duplicate a column right click it and choose Duplicate Column.
With the duplicated column you can right click it and choose Transform and then choose Month and then choose Start of Month see image below. I recommend using the Start as it is consistent.
You can also rename the duplicated column to make it more meaningful.
There are other transformations you can make. You may want to add multiple extra columns to handle the different ways you intend to analyse your data. See screenshots below for more options for years and days.
Alternative Method (thanks Wyn Hopkins)
This is the same method, but without using the right click.
Select the Date column and the click the Add Column tab.
On the far-right of the ribbon click the Date icon drop down. This allows you to select the type of date column to add – see below.
Power Query is similar to Excel’s macro recorder in that it records your changes (Steps) in the sequence that you make them. Those steps are then repeated in that same sequence whenever you refresh your data. So instead of continually fixing your data you do it once and record the steps and then let Power Query do all the work in the blink of an eye.