How to convert text to date

Hi ,


 I have a text formatted line item which is a date .I want to convert it to Date format .


I tried to break it into three Year ,Month and day using


Value(RIGHT(Line item,4))

Value(Mid(Line item,4,2))

Value(LEFT(line Item,2))


It works for 12/10/2012 but what about 2/3/2012 ??


Note -- Can't change source text to date format .

Best Answer


  • You have to identify the day, month, year using the seperators (/). Find the seperator position using the function FIND. Seperate the text between the seperators, using MID.


    You have to use a combination of FIND, MID and LEN functions to do it.




  • @Misbah 


    Thanks for detailed explanation. Looks like it will solve my problem . 


  • Thanks for this solution!


    Modification for DateTime Text String

    My text line item was formatted as DateTime (e.g., 8/3/2021 4:52:47), so I modified Misbah's solution to the following:





    1. Used the MID function to find the year - found the second "/" and added a +1 to set the start position.
    2. Flipped the Day and Month calculations.