Get the file Here: [ Ссылка ]
When you learn to group dates in Pivot tables in Excel, they only teach you how to do it if the original table brings the data in the correct format. It is very annoying when Excel does not allow us to do our grouping of dates in the pivot table and it does not tell us why. In this video we will see the different cases in which we cannot perform the grouping and how to solve them.
We start with the classic case in which the grouping is 100% feasible and the Pivot table, if we have Excel 2016, does it alone. In case of Excel 2010 it will only be necessary to do the grouping by hand, but it will give us the same result of grouping by months and quarters.
We continue with the case in which we have an error in our dates. With only one error in the original table is enough to ruin our entire group. In this case it is one error out of 30,000. You must realize that the filters in Excel and the alignment of our data in the original date column can tell us what the problem is. When correcting it, it is only necessary to update the Pivot table, and that's it. Everything solved.
We will cover the case in which we have dates saved as text. They are easily identifiable because they are aligned to the left in our column of dates. To fix it, it will only be necessary to use the text table in Excel columns. Although it was not the original intention, text in columns can clean this type of situations in which the date is converted into text. In the end we only update our Pivot table and you can now group the dates. You must copy and paste as values the data column corrected on itself to work.
We will also see how sometimes Excel can "uncovert" our date formatted dates and save the date as a number. This is a huge problem to group dates in the pivot table, so it will be necessary to convert our data to date. It's very simple, it's just a matter of changing the format.
We will see how the dates can sometimes be saved as a serial number, very much in the Japanese format and saved as text. To fix this, we will again use Text in Columns, only now we will use its Data Format function in Columns to convert this AMD date (year, month, day) to a date that Excel can use. You must copy and paste as values the data column corrected on itself to work.
When the date is broken in columns of year, month and day, we can use the function = Date in Excel, or function date, to be able to gather the data in a single date that brings date format. This should be done in an additional column. The result of the date function has a correct format that allows it to be grouped in a Pivot table.
To almost finish we will see how to take advantage of the vlookup formula (Vlookup) to convert static months into date formats. We will see that it is necessary to have a table where the vlookup can perform the date search and then pass it to our original data table. This transformation through vlookup will be sufficient for our purposes.
Finally, we will see how we can use the approximate search in vlookup to be able to group our dates in special periods, such as Christmas, back to school and so on. In this example we will use the signs of the zodiac and with approximate vlookup we will assign the dates. It will be necessary to have the periods defined for each year in our Excel table.
