Date Time to Date
May 15, 2018 - by Bill Jelen
Ian in Nashville gets data every day from a system download. The date column contains Date+Time. This makes the pivot table have multiple rows per day instead of a summary of one cell per day.
This article shows three ways to solve the problem.
The figure below shows the Date column with Date plus time.
One solution is to fix it in the pivot table.
- Drag the Date field to the Rows area. It shows up with Date+Time.
- Select any cell that contains a date and time.
- On the Pivot Table Tools Analyze tab, choose Group Field.
- In the Grouping dialog, select Days. Unselect Months. When you click OK, the pivot table will show one row per day.
Another tedious solution is to add a new column after the download. As shown in this figure, the formula in column J is
A date+time is an integer day followed by a decimal time. By using the INT function to get the integer portion of date+time, you isolate the date.
This will work fine, but now you have to add the new column every time you download the data.
My preferred solution is to use Power Query. Power Query only works in Windows editions of Excel. You can't use Power Query on Android, IOS, or the Mac.
Power Query is built in to Excel 2016. For Excel 2010 and 2013, you can download Power Query from Microsoft.
First, convert the downloaded data to a Table using Ctrl + T. Then, on the Data tab, choose From Table/Range.
In the Power Query grid, choose the Date column. Go to the Transform tab. Open the Date Type dropdown and choose Date. If you get a message about Replace Step or New Step, choose Replace Step.
On the Home tab of Power Query, choose Close and Load.
Your result will appear on a new sheet. Create the pivot table from that sheet.
The next time you get new data, follow these steps:
- Paste the new data over the original data set.
- Go to the sheet with the result of Power Query. Widen the Query panel so you can see Refresh. Click Refresh.
- Refresh the pivot table.
Download Excel File
To download the excel file: date-time-to-date.xlsx
To further improve the Power Query process:
- Make sure that the download workbook is always saved in the same folder with the same name.
- Start from a blank workbook. Save As with a name like DownloadedFileTransformed.xlsx
- From the blank workbook, Data, From File, From Workbook. Point to the file from Step 1.
- Choose Clean Data. Transform the Date/Time to a Date. Close & Load.
- Right-click the Query Panel and choose Properties. Choose Refresh Data When Opening the File.
- Each day, go to work. Get your coffee. Make sure a new file for #1 is in the folder.
- Open your DownloadFileTransformed.xlsx workbook. It will have the new data and the dates will be correct.
Excel Thought Of the Day
I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:
"Validation is your friend."
Title Photo: Charisse Kenion on Unsplash