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.

- Delete all spaces
- Trim extra space
- Substitute separators of Date like "-" or "/" or "/" or "." with nothing
- Extract Date, Month and Year using LEFT, MID, RIGHT formula
- Construct Date using DATE formula

I have 2 solutions for this problem.

- 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.
- 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.

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"

=IF(ISNUMBER(NUMBERVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(CLEAN(A1))," ",""),"-",""),"/",""),".",""),"\",""))),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(CLEAN(A1))," ",""),"-",""),"/",""),".",""),"\",""),"Date has Text")

2nd Formula then converts this extracted value to Date

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

=IF(LEN(B1)=8,DATE(RIGHT(B1,4),MID(B1,3,2),LEFT(B1,2)),IF(LEN(B1)=7,DATE(RIGHT(B1,4),MID(B1,2,2),LEFT(B1,1)),"Not Date"))

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

=IF(LEN(B8)=8,DATE(RIGHT(B8,4),LEFT(B8,2),MID(B8,3,2)),IF(LEN(B8)=7,DATE(RIGHT(B8,4),LEFT(B8,1),MID(B8,2,2)),"Not Date"))

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.

Function AdarshDateFromText(dateString As String, Optional Indian As Boolean = True) As String '''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Created by : Adarsh Madecha ' Created on : 15 December 2015 ' Version : 1.0 ' Copyright : All rights reserved. ' Website : madrecha.com ' Blog : adarshmadrecha.blogspot.com ' Twitter : @adarshMadrecha ' If you want to share this code, ask that person to visit - adarshmadrecha.blogspot.com '-----------Purpose of this fucntion------------------ ' Background: Many a times when we (CA/ CS/ ICWA) recieve data from client in excel or after conveting data to excel, Date columns are very difficult to deal with ' for this reason, I have created this fucntion, which will help in converting date from Text format to Actual date ' Using : Fuction accepts 2 parameters ' : 1st Fucntion - Text where data resides ' : 2nd Function - Whether Date in text is in Indian format or not ' : if Date is in format of DD MM YYYY (i.e. indian format), you need not specify any parameter. ' : if Date is in format of MM DD YYYY (i.e. American format), you need to specify 2nd parameter as FALSE. '''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim tempText As String 'For Removing all caracters Dim tempNumber As Double 'For checking we have only text ' Clean and Trim tempText = Application.WorksheetFunction.Clean(dateString) tempText = Application.WorksheetFunction.Trim(tempText) ' Delete . \ / - and Space tempText = Replace(tempText, " ", "") tempText = Replace(tempText, "/", "") tempText = Replace(tempText, "\", "") tempText = Replace(tempText, ".", "") tempText = Replace(tempText, "-", "") 'tempNumber = Application.WorksheetFunction.NumberValue(tempText) Dim isTempNum As Boolean 'Variable to store result isTempNum = IsNumeric(tempText) 'If the result contains any text, then return and exit If isTempNum = False Then AdarshDateFromText = "Date has Text" Exit Function End If 'Continnue if all the result is Number Dim tempDate, tempMonth, tempYear As String ' Get Date If Indian Then If Len(tempText) = 8 Then tempDate = Left(tempText, 2) ElseIf Len(tempText) = 7 Then tempDate = Left(tempText, 1) Else AdarshDateFromText = "Date is not proper" Exit Function End If Else If Len(tempText) = 8 Then tempDate = Mid(tempText, 3, 2) ElseIf Len(tempText) = 7 Then tempDate = Mid(tempText, 2, 1) Else AdarshDateFromText = "Date is not proper" Exit Function End If End If 'Get Month If Indian Then If Len(tempText) = 8 Then tempMonth = Mid(tempText, 3, 2) ElseIf Len(tempText) = 7 Then tempMonth = Mid(tempText, 2, 1) Else AdarshDateFromText = "Date is not proper" Exit Function End If Else If Len(tempText) = 8 Then tempMonth = Left(tempText, 2) ElseIf Len(tempText) = 7 Then tempMonth = Left(tempText, 1) Else AdarshDateFromText = "Date is not proper" Exit Function End If End If 'Get year tempYear = Right(tempText, 4) AdarshDateFromText = DateSerial(tempYear, tempMonth, tempDate) End FunctionIf 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

Labels: Excel, Excel Tips