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
-
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
Hope that helps
Misbah
8
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
0 -
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:
Notes
- Used the MID function to find the year - found the second "/" and added a +1 to set the start position.
- Flipped the Day and Month calculations.
0 -
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)
0