Dates not grouping in pivot tables in Excel - Fix those mistakes and more
Вставка
- Опубліковано 28 лип 2024
- Get the file Here: s3.amazonaws.com/a2excel.com/...
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.
Bro that text to columns move is insane!! Fixed my whole model thanks!!!!
Was struggling for hours with an export that would not group who knew text to columns would fix it, You are a life saver best video ever!!
Wow! that was simply awesome - the flow, the content, and the help I got from this video. Thanks for the video!
Hands down to you my brother, you win the Internet Today!!!!! Best Tutorial ever
Thank you sooo much for this! I felt like I'd lost my mind when my dates stopped grouping automatically. Your video saved me from a reporting pickle.
Hope you're well! thanks again!
This is a Masterpiece..Thanks for sharing knowledge
YOU are AWESOME, thank you so MUCH!
Very good guidance on solving my problem with large data that i couldn't group the date that look exactly like a date!. The text to column solved the mystery. Thanks!
Helped immensely, thank you!
Y're a GENIUS!!!!.......GOD BLESS YOU!!!
thanks very much for sharing this. you definetely saved my day
Best explanation on the entire internet!
Ultimate Tutorial ..was very much helpful
This video helped me a lot!! Thank you so much!!!
Text to column did it for me. Thank you so much!
thank you , that was very helpful
So helpful, thank you!
Thanks for the excellent guidelines.
Excellent Flow, thank you!!
THANKS BROTHER NOW IT IS OK!!!!! THANKS A LOT FOR OUR TUTORIAL!!!!
This is great. Can you also show how to group the dates when we actually put date in filter segment of a pivot table?
You are amazing!!! Thank you so much
Thank you SOOOOOOOOOOOOOOOOOO much, you saved me.
life Saver video, thanks a lot!
This Video is simply awsome, i got one issue in my excel but cleared with your video. thats wow.
Well explained .Thanks
thank you very much the text column was the answer to my problem.
Très intéressant merci
Got solution in a minute...... thank you
Thanks for the video. I finally solved my errors in grouping a date :)
Awesome video man
Thank you very much. I've been stuck in the error for an hour without any clue...
You're a genius!!!
My problem solved Thank you
You're a star! 👍
you are the man!
Thanks for the vid! Can you also teach me how to prevent pivot table from undoing my default cell background? Mine is GRay, and it keeps on removing cells below the pviot table every time I filter it. I've notice that yours is not changing, or perhaps you only removed gridlines? thanks!
IT WORKED THANKS
very usefull thank you
Nice video, thanks! you cover everything there, though... and you mightfind this one interesting... I have the case where dates are correctly recognised as such in the source data, with proper format,but get screwed up in the pivot table. I am thinking that might be linked to the fact that I have excel set for US formatting and need to enter dates values as M/D/Y but I formated the dates in the source data to be shown as DD-MM-YY (way more logical it feels). Any ideas or tips you may have there? struggling for quite some time on this now... THANKS!
Thank you!!!
Thank you so much
nice really helped me
very helpful
Just Brilliant
AWSOME YOU ARE MAN
Thanks man
THANK YOUUUUU
Oh my god! Thank you so much
Text to Columns...unbelievable! (after all these years....uuuggg) Cheers
You're cocky 😝 but very very good 👍. Thanks for saving me. I've spent an entire day trying to fix this. You're the best. I've done myself a favor by subscribing to your channel. Thanks 🙏 once again.
Mmmmuuuuuaaaaaaa 😘💋
Thank you very much😇, you help me to solve my problem with my data. it was a nightmare since 2 days to find a solution with my 10000 rows 🧐🤓
Great video! I do not like approximate match, as I do not know the algorithm and the result may be unexpected. Also, i need something, to give me weekly timeline with missing datapoints filled with zeros. Group by 7 days creates insanely long data labels for horizontal axis for chart.
Last resort is to merge datapoint dates with generated simple calendar with all dates in power query.
text to column fixed my issue. thanks
so we can't do grouping if using power pivot?
thank you thank you thank you
hi! is it possible to have a date filter after grouping the dates by years? thanks. i can't move on with this error.
is it possible to put dates in value field of pivot table
Thank you my friend! Good stuff! IVery much like your sense of humor. Please allow me to share one more simple way of doing "Only Month" without vlookup, by using formula "=datevalue(A2&1)"🙂
Hi, I have a problem in pivot table date grouping.
Before the date grouping in the pivot table, the date is a number e.g. 44186 (12/21/2020). However, when I dragged it to the pivot table, and then to group it into year, quarter, month and date. The date format is changed from a number to a text “Dec-21”. I have no idea why it is changed to text, and how can I keep it in number unchanged? I hope I make myself clear and you understand what my problem is. Thanks.
does this work the same when your source table is crosstabbed by date (dates are the column headers, not a column value)? i cant seem to group my column headers as they are showing as "sum of" fields even though nothing is being grouped
Companies that work on a fiscal calendar year instead of a normal calendar year is my special period issue. Unless there is another way? This will work! Thank you!
Legend
on the pivot range i have formula, due to which i am not able to group per month and year wise, kindly help to overcome it sir, very often i need to change the data range only then it is working
If in my dates column I have some cells where the dates is not available so they are empty, how can I group them in Pivot table? Cause it keeps telling me the error "Cannot group this field"
I can do this easily in the local app. But the online version does not show a [Group] option in the right-click menu. What now?
I have a file containing data and a column of date rows, but no matter what I do; Excel refuses to group. Tried everything: the settings in options to allow/disallow automatic grouping, going trough all of my cells to make sure they are dates, do the text-to-columns conversion thingy to dates, ... nothing helps. :(
I got Error every time i refresh the pivot table date are replace with word formula Like formula 01 and grouping is still my problem
Not sure why but I can not group, nothing happens the dates stay the same for some reason.....???? help
God bless you😅
❤️❤️❤️❤️
Misatkes?
Not what I needed at all. UA-cam algorithm fails again
Holy crap the audio difference between your voice over and brand intro is insane - I nearly had a heart attack. Good video though.
T
Any vids on caveman pivot tables?
Right is correct, left is wrong. Nothing political here😂
So long
Could you maybe leave out the pretentious attitude and focus on the learning?