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

Answers

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

     

    Thanks

    Arun

  • @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:

     

    Chmacpherson_0-1655489471957.png

     

    Notes

    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.
  • Just a few corrections for US format Dates with a little bonus if you just need all dates in a month to show the 1st of the month:

    Opening Date - Find /

    FIND("/", Opening Date, 0)

    Opening Date - Find //

    FIND("/", Opening Date, 'Opening Date - Find /' + 1)

    Opening Date - Day-Comm

    VALUE(MID(Opening Date, 'Opening Date - Find /' + 1, 'Opening Date - Find //' - 'Opening Date - Find /' - 1))

    Opening Date - Month-Comm

    VALUE(LEFT(Opening Date, 'Opening Date - Find /' - 1))

    Opening Date - Year-Comm

    VALUE(RIGHT(Opening Date, LENGTH(Opening Date) - 'Opening Date - Find //'))

    Opening Date (Date)

    DATE('Opening Date - Year-Comm', 'Opening Date - Month-Comm', 'Opening Date - Day-Comm')

    Opening Date (Begin Month)

    DATE(VALUE(RIGHT(Opening Date, 4)), VALUE(LEFT(Opening Date, FIND("/", Opening Date, 1) - 1)), 1)