Function for Format Conversion

Hi all,

I just want to share a simple table as a summary of the functions needed to convert data across formats in Anaplan.

Is not a big deal, but I hope it comes in handy to some of you.

Anaplan Format Conversion.JPG

* Transform Text into Date is not that straight forward since it is needed to chop the date in 3 chunks: the day (DD), the month (MM) and the year (YYYY), use VALUE to turn them into numbers and then use the function DATE to fetch the date.

Kind regards,

Alex.

Accepted answers

All comments

CallumW

Thanks for sharing, Alex!

Is it worth adding that changing a date to text format requires a DAY, MONTH, YEAR function, (split into separate line items for best practice) and then a TEXT function on each line item to piece the date back together?

I might add a suggestion to get a function added to simplify this process.

CallumW

**Update - request for function here, please upvote!:

https://community.anaplan.com/t5/Idea-Exchange/New-Function-To-Convert-Date-Format-to-Text-Format/idi-p/53134

AlejandroGomez

Hi Callum,

good catch! Indeed, moving from DATE to TEXT is not straight forward either since there is not a function yet for it. I have up-voted the request that you have shared.

thanks!

Alex.

DavidSmith

All of the above is the reason why it is so important to bring dates in as date formats in the first place. Not only do text formats take up more space and memory, but it is so much extra work to do to re-format.

Try and unify on the format for all imports (e.g. YYYYMMDD) and use that for all dates to avoid this issue.

Even for user driven uploads, it is better to add another column/row in the file to format the date in a "friendly" format

David

DavidSmith

@CallumW

Why do you need to change the Date to Text?

David

CallumW

Hi David,

This would be to have the ability to provide a date or a text value in a cell, mainly for reporting purposes.

There have been a number of scenarios where a user has requested that if a condition is met, pull a date otherwise pull a text value (or a number of different text values).

DavidSmith

Try, where possible to push back on requests like this though. We all know now, or should know, that we should avoid text formatted field whenever and wherever possible.

This is where we have to get a little creative in giving alternative solutions to users, for the greater good!!

David

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…