How to convert text to date

shadmani

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 .

Accepted answers

Misbah

@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

Hope that helps

Misbah

All comments

ArunManickam

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

@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

Hope that helps

Misbah

shadmani

@Misbah

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

Chmacpherson

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.

allen

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)

Swilson84

Thank you for this solution! It's really helpful and I will use it in the future.

Quick Links

Can you help?

Unanswered questions

Importing Ragged Hierarchies
Hello - I have a 2 level ragged hierarchy (yes, it's a chart of accounts), that for reporting purposes is used in 4 different models (yes, it's a P&L). What is the best way set this to allow one to be the "source of truth" and then import between the four models without creating actions that include ignores? It seems like…
Where can I find export file after running export process using machine task in a workflow?
I am testing export actions using workflow but its not generating any file when I am running the workflow, but the same process is generating export file when ran from the Actions→Process section. Is their any issue with the workflow functionality? Also, If I want to generate a file after completion of workflow and pull…
Automating Flat File Uploads to Anaplan Data Orchestrator (ADO) – Best Practices and Methods
Hello Anaplan Community, I am seeking guidance on the most effective ways to automatically upload flat files to Anaplan Data Orchestrator (ADO). Currently, our process involves loading flat files to an Anaplan Datahub using Anaplan APIs and custom Python scripting. With Anaplan Data Orchestrator's capabilities for data…