I have a line item that contains the date in a Text Format(examples below). Is there a formula that can convert these numbers to a Month Format?
201800120180022018003201800420180052018006201800720180082018009201801020180112018012
Hi,
I think the formula you wrote gives a correct result. Here's an alternative way with a short explanation.
PERIOD function converts date to a time period. To create values for time periods with this function, we need a date from each period. This can be achieved by creating dates with DATE function which needs three numeric parameters (year, month, day).
How to create the date: From the source line item (text) we can fetch year with LEFT(Input date, 4) and month with RIGHT(Input date, 2). If the source line item is numeric, add TEXT() around input date. Day parameter can be hard coded to any value between 1-28 because each month have those days. I'm going to use value "1". Because DATE function needs numeric parameters, we need to convert these to numbers with VALUE function: VALUE(LEFT(Input date, 4)) and VALUE(RIGHT(Input date, 2)).
So the date formula will be: DATE(VALUE(LEFT(Input Date, 4)), VALUE(RIGHT(Input Date, 2)), 1)
Add PERIOD function to convert this to any time period (for example month formatted line item). The final formula will look like this:
If source is text: PERIOD(DATE(VALUE(LEFT(Input Date, 4)), VALUE(RIGHT(Input Date, 2)), 1))
If source is number: PERIOD(DATE(VALUE(LEFT(TEXT(Input Date), 4)), VALUE(RIGHT(TEXT(Input Date), 2)), 1))
Br,
Jaakko
This formula is one way to get at what you need:
FINDITEM(Time, (IF VALUE(RIGHT(Input Date, 2)) = 1 THEN "Jan" ELSE IF VALUE(RIGHT(Input Date, 2)) = 2 THEN "Feb" ELSE IF VALUE(RIGHT(Input Date, 2)) = 3 THEN "Mar" ELSE IF VALUE(RIGHT(Input Date, 2)) = 4 THEN "Apr" ELSE IF VALUE(RIGHT(Input Date, 2)) = 5 THEN "May" ELSE IF VALUE(RIGHT(Input Date, 2)) = 6 THEN "Jun" ELSE IF VALUE(RIGHT(Input Date, 2)) = 7 THEN "Jul" ELSE IF VALUE(RIGHT(Input Date, 2)) = 8 THEN "Aug" ELSE IF VALUE(RIGHT(Input Date, 2)) = 9 THEN "Sep" ELSE IF VALUE(RIGHT(Input Date, 2)) = 10 THEN "Oct" ELSE IF VALUE(RIGHT(Input Date, 2)) = 11 THEN "Nov" ELSE IF VALUE(RIGHT(Input Date, 2)) = 12 THEN "Dec" ELSE "") & " " & MID(Input Date, 3, 2))
This formula assumes that the input date (the list of numbers you provided) is formatted as text, that the first four characters represent the year, and the last two characters represent the month. It ignores the fifth characer. The line item where you place this formula will need to be formated as month time period.
Let me know if this works.
Thank you for the formula!
Question, when I dropped the formula into my line item it gave me this error in the image provided.
The formula references "Time Period Text" which is another line item is a module and is Text Formatted. Is there any way you know to work within the module and not have to create a hierarchy in the lists per the error message?
It is good practice to split formulae up into the component parts, especially if the line item in which they are contained has a large cell count. It is more efficient to have two line items than combine everything together with nested IFs. My recommendation would be to create a list containing 2018001 - 2018012. Create a simple mapping module mapping 2018001 to January 18. Then have a line item to find the "time list", e.g. TimeList LI=FINDITEM(TImeList, text), then have a second line item to pull in the month, e.g. TimeMapping.Month[lookup:TimeList LI]
Happy Modelling
David
Thank you, David. What format should the two line items you mention be in?
Hi
Let's assume you created the list of dates (2018001, 2018002 etc.) and called it Months List
The first line item would be formatted to Months List
The second line item is formatted as a time period (in your case month)
Hope that helps
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…
I'm trying to execute an Action to export data with the use of a Mapping in Informatica. But it gives us null rows .If we export directly from Anaplan , the export file has all the rows filled. Both exports have same number of rows but in the informatica export the values appear as blank. This issue is with only some…
