Microsoft Excel Tutorial: Excel Convert Date and Time to Just Date

Welcome to another episode of the MrExcel netcast, where we help you master Excel with our expert tips and tricks. In today's episode, we will be discussing how to truncate date and time to just a date in Excel. This is a common issue faced by many Excel users, and we have the perfect solution for you.

The question for today's episode comes from Ian, who attended my Power Excel seminar in Nashville. Ian is facing a problem where his downloaded data has both date and time in the same column, causing issues when creating a pivot table. Instead of getting one row per date, he is getting multiple rows for each date and time. This can be a frustrating and time-consuming problem, but fear not, we have a solution that will make your life easier.

In this episode, we will be using Power Query, which is a powerful tool built into Excel 2016. If you are using Excel 2010 or 2013 on a Windows computer, you can download Power Query for free. Power Query works on a table or range of data, so we will first convert our data into a table by selecting one cell and pressing Ctrl+T. Then, on the Power Query tab, we will select "Data" and then "From Table/Range". From there, we can transform the date column to just show the date, and then load it onto a new sheet.

The beauty of using Power Query is that it automatically updates when new data is added to the table. So, if Ian gets more data in the future, all he has to do is paste it into the original table and click "Refresh" in the Power Query tab. This will update the data in the table and the pivot table, making it a hassle-free solution. While there are other ways to solve this issue, such as using the INT function or grouping the pivot table, Power Query is by far the most efficient and convenient method. To learn more about Power Query and other Excel tips, check out my new book, "MrExcel LIVe: The 54 Greatest Excel Tips of All Time".

To download the workbook used in this episode, click on the link in the YouTube description. I want to thank Ian for bringing up this issue and attending my seminar in Nashville. And as always, thank you for tuning in to the MrExcel netcast. Don't forget to subscribe to our channel for more Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: [ Ссылка ]

You can help my channel by clicking Like or commenting below: [ Ссылка ]

#excel
#microsoftexcel
#excelhacks
#excelformula
#evergreen
#exceldates
#powerquery

This video answers these common search terms:
how to remove time after date in excel
how to remove time from a date in excel
how to remove time from a date in excel
how to remove time from date and time data in excel
how to remove time from date and time excel
how to remove time from date excel
how to remove time from date excel
how to remove time from date excel formula
how to remove time from date in excel
how to remove time from date in excel
how to remove time from date in excel
how to remove time from date on excel
how to remove time from date time inexcel
how to remove time in a date excel
how to remove time in excel date
how to remove time off an excel date
how to remove time on date in excel
how to remove time stamp from date in excel
how to remove times after dates in excel
how to remove times from dates in excel
how to remove timestamp from date in excel
how to remove the time from date in excel
how do i remove time from excel when it has date and time
date and time in excel how to remove time

Table of Contents:
(00:00) Introduction to Episode 2203: Truncate Date and Time to Just a Date
(00:14) Issue with Pivot Table and multiple rows for Date and Time
(00:34) Introduction to using Power Query to solve the issue
(01:02) Using Power Query to convert Date and Time to just Date
(01:30) Advantages of using Power Query for future data updates
(02:26) Alternative solutions to the issue
(03:29) Clicking Like really helps the algorithm

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: [ Ссылка ]
List of upcoming seminars: [ Ссылка ]

Join the MrExcel Message Board discussion about this video at [ Ссылка ]

свернуть