Select Page

Convert Text to Date when Importing Data to Excel

Convert Text to Date when Importing Data to Excel
Many a times, we have to import data which is given by client. When data is imported in excel, date is most challenging to work with. Date column is imported in excel as text field and this limits our analytic like – Filtering using date, Pivot table, Time line in Pivot table,  Date related formulas like Weekday, Month, etc.

In order to work with date, you will first need to convert text into date. Which involves following steps.

  1. Delete all spaces
  2. Trim extra space
  3. Substitute separators of Date like “-” or “/” or “/” or “.” with nothing
  4. Extract Date, Month and Year using LEFT, MID, RIGHT formula
  5. Construct Date using DATE formula

These steps takes too much time and more over if you have many data to import, you tend to get frustrated and your precious time is lost in data Manipulation instead of data Analytic.

I have 2 solutions for this problem.

  1. Use the below 2 formulas on the column where you have date – Useful if you have just once in a while a case where you need to convert Date from Text in Excel.
  2. Use below single Custom function for converting date in text form to Date form – Added advantage of using this method is that, you can use it anywhere and wherever you like, No need to change reference in the formula and easy to use.

1st Solution – Using formula
While writing the formula I have assumed that Date in text form is in cell A1, if your date resides in other cell, you will need to change reference accordingly. You will need 2 formulas to get date.
Assumptions :
Date resides in A1
1st Formula is in B1
2nd Formula is in C1

1st formula to remove all the extra “things”

2nd Formula then converts this extracted value to Date
If date is in the Indian format i.e. DD MM YYYY

If date is in the American format i.e. MM DD YYYY

2nd Solution – Using custom fucntion
You should know how to use / install custom function to use this OR you can just download the file (Link below) where I have installed the custom function.

If you don’t know how to use this code / custom function, just download the below file.
You can then import the data as you would usually do, in this excel file.
Download Adarsh Text to Date Custom Function

Once you have data in date format, you can use Convert Date to CST Date format, as discussed on this post 

About The Author

CA, ISA, CISA, BCAF. Friends call me Techno Savvy Chartered Accountant. I work at EY in System Audit

Leave a reply

Your email address will not be published. Required fields are marked *


Subscribe To my Newsletter

Subscribe To my Newsletter

Join the mailing list to receive the latest news and updates from the blog

You have Successfully Subscribed!

Pin It on Pinterest

Share This