Hi, I am trying to convert a date from Ex., May 5 2019 12:00AM TO M/DD/YYYY format. I have created a list for all 12 months to convert May to a 5 by referencing the Code column in the list of months I made. I now need to pull in the day and year and add the slashes. Can someone please help me with the formula build to pull in the /'s and day and year so the new date will read 5/5/2019?
Also needs to work when the day of the month is a double-digit like, Ex., 5/12/19.
Might be a dumb question, but where is this data coming from? Can't you clean the data on import to Anaplan so you don't have to do all of these conversions? If you change the date format in the file you are loading to YYYYMMDD, then you will have all of your information already.
The data comes originally from software called Yardi. It's unfortunately not possible to change the format of the source data. It would take a series of SQL query writing in order to correct the date formats and would be costly to have this done.
In the load interface of Anaplan, there is a time format mapper that should be able to handle this.
When you create the data import, go to the list of line items. For the date line item, you should have a boolean next to it, click on it. In there, you'll have a bunch of option. Choose the one that matches your source format and Anaplan will do the job for you.
There are a number of text formulas that can be combined to enable the extract of the day, month and year to allow you to use PERIOD(DATE()) to build up a time period formatted line item.
I will assume that you would like to use these entries to map a time dimension. Therefore, we will use the above formula to create the final time period formatted line item.
Taking your example; May 5 2019 12:00AM
First you must ensure that you load the date into a TEXT formatted line item as these formulas only work for text strings NOT numbers or dates.
Firstly, I would recommend amending your month list to only include the first 3 letters of each month ( Jan, Feb, Mar, Apr etc ) this is important later in the solution when we write the formula to retrieve the month number.
Also, add a property to the list and name it, 'month length.' For each month input the the number of characters in the full month name ( January = 7, February = 8, March = 5 etc ) this will provide a reference to allow us to extract the day number from the text string.
Retrieving the Month number.
We will use a combination of CODE(), FINDITEM() and LEFT().
Create a new line item called, 'Month Number,' formatted as NUMBER. Assuming you have amended you month list use the following formula. =CODE(FINDITEM(MonthsList.LEFT(DateLineItem,3))) This formula is extracting the first three characters of the date line item, retrieving the relevant list item from the Months list and returning the corresponding code.
Retrieving the Daynumber
We will use a combination of MID(), LEN() and the month length property to extract the day number.
Create a new line item called, 'Day Number.' Formatted as NUMBER.
Populate this line item with the following =LEN(MID(DateLineItem,MonthsList.month length+1,2))
Retrieving the Yearnumber
We will use a combination of LEN(), FIND() and MID()
Create a new line item called, 'Year Number,' formatted as NUMBER
Using the day number line item we can combine the above formulas to extract the year number.
The three formulas above should enable you to retrieve the the YEAR, MONTH and DAY numbers as from the text formatted date.
From here you can either convert them into a DATE or PERIOD formatted line item. If you are unsure which one you will need later in your modelling I recommend converting into both.
Create two line items called, 'Date' and 'Period,' respectively. Set the format of 'Date' to date and 'Period' to whichever time period you require ( most likely months ).
To convert to date use =DATE(Year Number, Month Number, Day Number)
To convert to period use =PERIOD('Date')
You may need to tweak these slightly to fit your naming convensions but the principles outlined here should enable you to convert you date into a date or time period line item.
However, I would recommend performing a second import from this module into one which contains a time dimension. Further modelling will be more efficient and effective when utilising the inbuilt Anaplan time dimension rather than relying on line items to define time/dates.
Good luck, Chris
Chris Heathcote Bedford Consulting
Gold Partner and Regional Partner of the Year 2021, EMEA