MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Date Time to Date

May 15, 2018 - by Bill Jelen

Date Time to Date

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.

Keep the dates, get rid of Time.
Keep the dates, get rid of 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.
Choose Days. Unselect Months.
Choose Days. Unselect Months.

Another tedious solution is to add a new column after the download. As shown in this figure, the formula in column J is =INT(D2).

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.

Solution 2: Add a new column every day.
Solution 2: Add a new column every day.

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.

Convert the data to a table
Convert the data to a table

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.

Truncate the date+time to date.
Truncate the date+time to date.

On the Home tab of Power Query, choose Close and Load.

Return the data to Excel
Return the data to Excel

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:

  1. Paste the new data over the original data set.
  2. Go to the sheet with the result of Power Query. Widen the Query panel so you can see Refresh. Click Refresh.
  3. Refresh the pivot table.
Return the data to Excel
Return the data to Excel

Watch Video

Download Excel File

To download the excel file: date-time-to-date.xlsx

To further improve the Power Query process:

  1. Make sure that the download workbook is always saved in the same folder with the same name.
  2. Start from a blank workbook. Save As with a name like DownloadedFileTransformed.xlsx
  3. From the blank workbook, Data, From File, From Workbook. Point to the file from Step 1.
  4. Choose Clean Data. Transform the Date/Time to a Date. Close & Load.
  5. Right-click the Query Panel and choose Properties. Choose Refresh Data When Opening the File.
  6. Each day, go to work. Get your coffee. Make sure a new file for #1 is in the folder.
  7. Open your DownloadFileTransformed.xlsx workbook. It will have the new data and the dates will be correct.
Have the data update every time you open the file.
Have the data update every time you open the file.

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

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.