Converting Text to Date and Date to Text

jziemer

Author: Julie Ziemer is a Certified Master Anaplanner, Anaplan Community Boss, and Solution Leader/Business Analyst at Akili.

While Anaplan has specific functions to convert between data types, converting Text to Date and Date to Text are two commonly searched topics on Community and they require the use of several functions to achieve the desired results. This article is meant to summarize the best practices for conversion.

First, let’s look at Text to Date.

Text to Date

In this example, we will assume your text formatted date is coming in with a separator such as “/” (e.g. 6/15/24).

Step 1: Best practice is to avoid hardcoding parameters in syntax, so we will first want to use the “FIND” Function to identify the number sequence of the “/” characters.

Step 2: Use the LEFT, MID, and RIGHT functions to dissect the Month, Day and Year into separate text formatted line items.

Step 3: Since the Anaplan DATE function requires numeric format for its parameters, Use the VALUE function to change the text month, day and year into number format.

Step 4: Finally, use the DATA function to create the date formatted line item.

Of course you could do all of this by combining the formula into one line item after you have done the “find” functions in Step 1, DATE(VALUE(RIGHT(Text Date, 4)), VALUE(LEFT(Text Date, 'Find "/"' - 1)), VALUE(MID(Text Date, 'Find "/"' + 1, 'Find 2nd ''/''' - 'Find "/"' - 1))), however it is generally best practice to break the calculations into smaller parts (I find I often need to refer to a single month or year in other formulas in either text or numeric format), and it is always best to do this conversion in a System module that be referenced many times.

Note that if your text formatted Date uses a date format that has the month in alphanumeric format (e.g. 15-JUN-2024), you will first need to create a mapping of months to numbers to reference in your formulas using a “Month Text” list (see below example), and then modify the formulas to substitute the month mapping and the “FIND” formula to find a “-“ vs. “/” in the steps above.

Related discussion forum posts:

• How to convert text to date
• Text to Date conversion

Date to Text

Using the TEXT Function and the MONTH, DAY, YEAR functions, you can easily convert a Date formatted line item to a Text formatted line item using the following formula:

TEXT(MONTH(DATE FORMAT)) & "/" & TEXT(DAY(DATE FORMAT)) & "/" & TEXT(YEAR(DATE FORMAT))

Again, you should always use a System module to do this conversion so that it can be referenced often. Also, you may want to break this formula apart into separate line items to split the Month, Day, and Year values if you need to reference the day, month or year separately, or if you are running performance issues related to the date conversion formula.

Related discussion forum post:

• How to convert Date to Text


Questions? Leave a comment!

Dikshant

Good one @jziemer - One suggestion: The hardcoded / can be replaced with a text line item which will dynamically find the date separator by using FIND function to determine if day, month and year is separated by a slash, hyphen, or comma.

jziemer

Agreed @dlkshant! Similar to find formulas in first section. Thanks for pointing that tip out!