Date Conversion

michael.chefer

 

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? 

 

2018001
2018002
2018003
2018004
2018005
2018006
2018007
2018008
2018009
2018010
2018011
2018012

Jaakko

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

 

bdeaton

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.

michael.chefer

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?  

 

 

DavidSmith

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

michael.chefer

Thank you, David. What format should the two line items you mention be in? 

DavidSmith

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

David