Last month, I wrote an article about Anaplan’s Idea Exchange and some of the ideas that have been proposed by the community. One of the ideas I wrote about was about how Anaplan should implement an easier way to convert date format to text format, and text format into date format. I showed in that article what converting between these data types currently looks like. Since it is a tedious process, I decided to make it into its own blog post for beginning Anaplan modelers to be able to more easily reference.
Use Cases
Obviously, the fact that Anaplan has yet to have implemented simple formulas to achieve these tasks shows that converting between these two data types is not an overly common need or that important on their list of things to do. For the most part, this is correct; however, there are a few use cases that I can think of off the top of my head.
Converting date format to text format can be useful in the process of creating a code to uniquely identify new transactional data list items. After creating the text formatted date, the model builder would then concatenate it with some other uniquely identifying information and use this as the code for the item.
Converting text into date format could be used for taking a string of raw data imported into the Anaplan model and un-concatenating it to get the date.
Converting from Date Format to Text Format
The easiest way to do this is by taking advantage of the DAY(), MONTH(), and YEAR() functions.
As shown in the screenshots above, create three number formatted line items to break down the date into its component numbers. Then in a final text formatted line item, simply concatenate the components together in the correct order and with “/” in between. Make sure to use an IF ISNOTBLANK() to test the original date formatted line item so a blank date will translate into a blank text box.
Converting from Text Format to Date Format
Unfortunately, text to date format is much more complex. Since writing a date in text format is not standardized unlike a date in a date formatted line item, your conversion may look slightly different. There are also multiple ways to use the formulas to get the same end result.
Start with the text formatted date. Breaking down the text into its component numbers will require using the slashes as indicators as to the length of each component. Find the first “/” with the FIND() function as shown above. Then find the character length in the entire text cell.
Then use these three line items inside a RIGHT() function to give us the date minus the month and first slash.
Find the last slash in the previous line item by using the FIND() Function. Then simply make a line item for each component using the LEFT() and RIGHT() functions within the VALUE() function and the previous line items. Finally, put it all together into date format using the DATE() function.
Another Option
There is a third option taking advantage of import processes. However, this method is not really any quicker to set up, and it does not follow PLANs standards. It just shows that there are multiple ways to solve a problem in Anaplan, some ways being better than others.
As a side note, if you ever build something like this, when applicable make sure you also have an import process to clear data before importing new data.
Interested in hiring our Concessio consultants to help your Anaplan implementation? Visit our website’s Anaplan page here.
no replies