Function for Format Conversion

AlejandroGomez

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. 

  * 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. 

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

OK

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