Date Format Conversion Formula

jk1nn1ck_*20973

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.

 

Thanks,

nathan_rudman

Hi,

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.

 

 

rob_marshall

@jk1nn1ck ,

 

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.

 

Thanks,

 

Rob

jk1nn1ck_*20973

Hi Robert,

 

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.

 

jnoone

Hi - I've attached some calcs which should work for your purposes as a workaround.  I tested different scenarios and seems to do the trick.  

 

Hope that helps.

 

Jt

ChrisAHeathcote

@jk1nn1ck 

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 Day number

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 Year number

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.

 

=LEN(MID(DateLineItem,FIND(DateLineItem,Day Number)+1,4))

 

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

ChrisAHeathcote

@jk1nn1ck  

@nathan_rudman 

A much simpler solution if you are able to successfully match the options against the import and one which should always be sought before any other more convoluted alternative.

Chris

DavidSmith

Can I just add, from a best practice and performance perspective, it is better to split the component parts of formulas

 

so taking the following:

CODE(FINDITEM(MonthsList.LEFT(DateLineItem,3)))

 

Split into 

line item 1 = LEFT(DateLineItem,3)

line item 2 = FINDITEM(MonthsList, line item 1)

line item 3 = Code(line item 2)

 

You might find that you can reuse some of the other line items - "calc once, reference many times"

 

David

ChrisAHeathcote

Hi David,

Yes, I agree. As soon as you find you are embedding more than 2 formulas alarm bells should be ringing.

Chris