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"
 =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 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 

Labels: ,