How to convert text to date

shadmani
Contributor

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 .

1 ACCEPTED SOLUTION

Accepted Solutions
Misbah
Moderator

@shadmani 

 

Avoid hardcoding the parameters of the syntax - There are many functions that can be put to use here as @ArunManickam already mentioned. FIND, MID,LENGTH,VALUE,RIGHT,LEFT,DATE

 

See below

Misbah_1-1580370763119.png

 

Misbah_0-1580370721864.png

Hope that helps

Misbah

View solution in original post

4 REPLIES 4
ArunManickam
Master Anaplanner/Community Boss

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
Moderator

@shadmani 

 

Avoid hardcoding the parameters of the syntax - There are many functions that can be put to use here as @ArunManickam already mentioned. FIND, MID,LENGTH,VALUE,RIGHT,LEFT,DATE

 

See below

Misbah_1-1580370763119.png

 

Misbah_0-1580370721864.png

Hope that helps

Misbah

shadmani
Contributor

@Misbah 

 

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

 

Chmacpherson
Contributor

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.