Ian in Nashville gets data from the system. The date column has date and time.
This really screws up the pivot table, because instead of daily dates, he gets time.
One solution: Group the pivot table by Date
Better solution: Power Query
Make the downloaded data set in to a table with Ctrl+T
Data, From Table. Select the Date Time column and transform to Date
Close and Load
Build the pivot table from that
The next time you get data, paste to original table. Go to Query. Refresh. Refresh the Pivot Table
To download this workbook: https://www.mrexcel.com/download-center/2018/05/date-time-to-date.xlsx
List of upcoming seminars: Excel Seminar Schedule
This really screws up the pivot table, because instead of daily dates, he gets time.
One solution: Group the pivot table by Date
Better solution: Power Query
Make the downloaded data set in to a table with Ctrl+T
Data, From Table. Select the Date Time column and transform to Date
Close and Load
Build the pivot table from that
The next time you get data, paste to original table. Go to Query. Refresh. Refresh the Pivot Table
To download this workbook: https://www.mrexcel.com/download-center/2018/05/date-time-to-date.xlsx
List of upcoming seminars: Excel Seminar Schedule
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2203: Truncate Date and Time to Just a Date.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question from Ian in my Nashville Power Excel seminar.
Look at this: Ian is getting a download from his system every day, and it has-- in the Date column-- it has Date and Time, and that is screwing things up because when Ian creates a Pivot Table, and puts Dates along the left-hand side, instead of just getting one row per Date, he's getting one row for every Date and Time.
Now, it would be possible to come here and say, Group Field, and group this to Day, click OK.
And, you know, that's an extra few clicks that I don't think we have to do.
And, so, for this Podcast, the next few Podcasts, we're going to take a look at Power Query.
Now, it's built into Excel 2016 here, under Get & Transform Data.
If you have 2010 or 2013 running in Windows, not on a Mac, you can download Power Query for free from Excel.
And Power Query wants to work on a Table or Range.
So I'm going to choose one cell of data, press Ctrl+T for Table, perfect.
And then, on my Power Query tab, Data, From Table/Range, I'm going to choose the Date column and Transform, and say that I want this not to be a Date and Time, but just a Date.
I'll choose Replace current, and then Home, Close & Load, and we get a brand new sheet form that has the data converted.
Now, from here, of course, we can summarize with a pivot and it'll be perfect.
The advantage, I think, of this method, Power Query, is when Ian gets more data...
So here I have new data, I'm going to take this new data, copy the data, and just come here to my old data and paste just below.
And, notice that end of table marker right now is in Row 474.
When I paste, the end of table marker moves down to the bottom of the new data.
Alright?
And this query is written based on the table.
Alright?
So, as the table grows.
Now, one hassle here is, if you're using Power Query for the first time, Power Query starts out that wide-- only half wide-- and you can't see the Refresh icon.
So, you have to drag it out here like this and get that great Refresh icon, and just click Refresh.
Right now, we have 473 rows loaded, and now 563 rows loaded.
Create your pivot table, and life is great.
Now, hey, look, I know it would have been possible to add a new column out here, =INT(D2)-- --of the date-- copy that down, copy and Paste Special Values.
But then you're on the hook for doing that every single day.
Alright?
So while there's at least three different ways we talked about in this video of solving it, for me, Power Query and, just, the ability to click Refresh, is the way to go.
Power Query, I talk about it in my new book, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
Wrap-up of today's Episode: Ian in Nashville gets data from a system every day, the Date column annoyingly has both Date plus Time-- it screws up his pivot tables, so he has to...
Well, three possible things: One, use the INT function, copy, paste values; second, just leave it as Date and Time and then group the pivot table by Date; or, a better solution, Power Query, makes the downloaded data set into a table with Ctrl+T, and then on the Data tab, choose From Table, select the Date and time column, go to the Transform tab in Power Query, convert it to a Date, Close & Load, build the pivot table from that.
Next time you get data, paste in the original table, go to the Query panel, click Refresh, and you're good to go.
Hey, to download the workbook from today's video, visit the URL in the YouTube description.
I want to thank Ian for showing up in my seminar in Nashville, I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question from Ian in my Nashville Power Excel seminar.
Look at this: Ian is getting a download from his system every day, and it has-- in the Date column-- it has Date and Time, and that is screwing things up because when Ian creates a Pivot Table, and puts Dates along the left-hand side, instead of just getting one row per Date, he's getting one row for every Date and Time.
Now, it would be possible to come here and say, Group Field, and group this to Day, click OK.
And, you know, that's an extra few clicks that I don't think we have to do.
And, so, for this Podcast, the next few Podcasts, we're going to take a look at Power Query.
Now, it's built into Excel 2016 here, under Get & Transform Data.
If you have 2010 or 2013 running in Windows, not on a Mac, you can download Power Query for free from Excel.
And Power Query wants to work on a Table or Range.
So I'm going to choose one cell of data, press Ctrl+T for Table, perfect.
And then, on my Power Query tab, Data, From Table/Range, I'm going to choose the Date column and Transform, and say that I want this not to be a Date and Time, but just a Date.
I'll choose Replace current, and then Home, Close & Load, and we get a brand new sheet form that has the data converted.
Now, from here, of course, we can summarize with a pivot and it'll be perfect.
The advantage, I think, of this method, Power Query, is when Ian gets more data...
So here I have new data, I'm going to take this new data, copy the data, and just come here to my old data and paste just below.
And, notice that end of table marker right now is in Row 474.
When I paste, the end of table marker moves down to the bottom of the new data.
Alright?
And this query is written based on the table.
Alright?
So, as the table grows.
Now, one hassle here is, if you're using Power Query for the first time, Power Query starts out that wide-- only half wide-- and you can't see the Refresh icon.
So, you have to drag it out here like this and get that great Refresh icon, and just click Refresh.
Right now, we have 473 rows loaded, and now 563 rows loaded.
Create your pivot table, and life is great.
Now, hey, look, I know it would have been possible to add a new column out here, =INT(D2)-- --of the date-- copy that down, copy and Paste Special Values.
But then you're on the hook for doing that every single day.
Alright?
So while there's at least three different ways we talked about in this video of solving it, for me, Power Query and, just, the ability to click Refresh, is the way to go.
Power Query, I talk about it in my new book, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
Wrap-up of today's Episode: Ian in Nashville gets data from a system every day, the Date column annoyingly has both Date plus Time-- it screws up his pivot tables, so he has to...
Well, three possible things: One, use the INT function, copy, paste values; second, just leave it as Date and Time and then group the pivot table by Date; or, a better solution, Power Query, makes the downloaded data set into a table with Ctrl+T, and then on the Data tab, choose From Table, select the Date and time column, go to the Transform tab in Power Query, convert it to a Date, Close & Load, build the pivot table from that.
Next time you get data, paste in the original table, go to the Query panel, click Refresh, and you're good to go.
Hey, to download the workbook from today's video, visit the URL in the YouTube description.
I want to thank Ian for showing up in my seminar in Nashville, I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
