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 Anaplanners, I’m working with a calculation module (Module A) that is dimensioned by a concatenated list, Time, and Native Versions. In this module, I am performing a subtraction using values from another module (Module B), which is dimensioned by Native Versions and a different concatenated list. The two concatenated…
I am trying to create a Net Debt / EBITDA formula at the bottom of one of our P&L income statements where, i have tried the MOVINGSUM function. However this does not work for summary line items which EBITDA is. What i would like is to be able to sum the previous 12 months EBITDA (Including the current period) numbers in a…
I have a selective access cost center list in a flat format that contains multiple hierarchy levels. I need to assign more than one cost center parent to a single user. While this can be done manually, I would like to know if there is a way to import this through a CSV file instead. Could you please confirm if this is…
