About this lesson
Use the text to columns button to separate the different parts of the date and the DATE function to put it back together as a proper date.
Download this lesson’s exercise file.Bank deposit exercise 2.xlsx
Importing data into Excel: fixing the date
Learn to recognize incorrect dates and fix them when importing files.
When to use
This is useful to know if you received a file from an external source and the date fields are seen as text and you don't want to retype the data.
- Excel regards dates as serial numbers, and auto aligns imported, recognized dates to the right-hand side of a cell just like numbers
- Different regional settings on computers and a range of other issues can cause dates to import as text
- The fix for this is usually linked to the problem
In the video
- Separate the elements of the date using text to columns
- Add the elements of the date together again using a DATE function
- Separate DATE functions for the two different date problems
Lesson notes are only available for subscribers.