Flash Fill was introduced in Excel 2013 for Windows, and it's a really handy tool for data cleansing tasks. One simple way is to use the new Flash Fill feature. There are several ways to solve this problem, and I'm looking forward to seeing your solution in the comments below. Our challenge is to convert the text to a date value. If you have some text at the bottom of the grouped dates, then those values will need to be converted to dates to use all the grouping features of a pivot table. The column for the filter list on the right contains dates, and the date values in the filter list are grouped into Year, Month, Day, Minute, Hour, Second. In the image below you can see the filter list on the left is all text values. Method #2: Filter Drop-down MenuĪnother way to see the data type is by using the filter drop-down menu. This is a very important concept that will help you cleanse your data and work with dates. The image below shows what we will see if the value is stored as Text.Īnd here is an image of the cell value that is recognized as a date.Ĭheckout my article and video on Dates in Excel to learn more. If the preview changes for number format, then the cell is recognized as a Date.If the preview of each number format has the same value as the cell contents, then it is Text.Click the Number Format drop-down on the Home tab of the ribbon.Here's an easy way to figure it out the data type of the cell using the Number Format Menu: We first need to determine if Excel is recognizing the value in the cell as text or a date. I also explain how to determine if Excel recognizes the value as a date, so let's look at that first. In the video above I share one solution using Flash Fill. There are several ways to go about this task in Excel. So, how do we convert the text into a date that Excel recognizes? You can also download the example file above. Here is the text for the date that you can copy/paste to Excel. Therefore, he cannot sort the column or use it with the date grouping feature of a pivot table. Excel does not recognize these cells as a date data type. He has a data set exported from a system that contains a column with a date & time. Mark, a member of The Pivot Ready Course, had a great question about converting text to dates. Text-to-Date-Challenge-Live-Training.zip Download Data Cleansing Challenge
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |