Need to convert Date into a particular format

Hi,

I have a line item formatted as date in the module like: "4/29/2020" and i want it to convert into "As of Apr 29, 2020". I tried myself with the below formula but didn't work, kindly advise:

"As of " & TEXT(MONTH(As of Date)) & "/" & TEXT(DAY(As of Date)) & "/" & TEXT(YEAR(As of Date))

Thank you!

Accepted answers

nathan_rudman

Hi

There's no function in Anaplan that will give you the letters of a month.
Thankfully, the usual Anaplan flexibility is here:

- create a line item period with a formula PERIOD() of the date which will give you something like Apr 18

- create a line item "month letters" with the following formula: LEFT(NAME(period),3)

- then use this LI in your formula

Misbah

@sharmagh

See if this helps

Note: This is not as per best practices. You can break the formula string into multiple line items

All comments

sharmagh

Sorry I applied this formula:
"As of " & TEXT(MONTH(As of Date)) & " " & TEXT(DAY(As of Date)) & "," & TEXT(YEAR(As of Date))

nathan_rudman

Hi

There's no function in Anaplan that will give you the letters of a month.
Thankfully, the usual Anaplan flexibility is here:

- create a line item period with a formula PERIOD() of the date which will give you something like Apr 18

- create a line item "month letters" with the following formula: LEFT(NAME(period),3)

- then use this LI in your formula

Misbah

@sharmagh

See if this helps

Note: This is not as per best practices. You can break the formula string into multiple line items

sharmagh

but i have period in weeks

nathan_rudman

that's why in my solution I use an intermediary LI so you can format it as the time period you want (month here)

sharmagh

I got the solution but this is not the way I am looking for, if possible please suggest me an appropriate way of doing this, below is the approach I followed:

I created another line item where i applied the below formula to get the month name and then concatenated with other values:
"IF MONTH(Date) = 1 THEN "January" ELSE IF MONTH(Date) = 2 THEN "February" ELSE IF MONTH(Date) = 3 THEN "March" ELSE IF MONTH(Date) = 4 THEN "April" ELSE IF MONTH(Date) = 5 THEN "May" ELSE IF MONTH(Date) = 6 THEN "June" ELSE IF MONTH(Date) = 7 THEN "July" ELSE IF MONTH(Date) = 8 THEN "August" ELSE IF MONTH(Date) = 9 THEN "September" ELSE IF MONTH(Date) = 10 THEN "October" ELSE IF MONTH(Date) = 11 THEN "November" ELSE "December""

Misbah

@sharmagh

This is not necessary - will be taxing to the model performance. Just break the formula as I said earlier you will be able to see the output

You can change the Time period from months to weeks in intermediate line item and then use that in final line item

rob_marshall

@sharmagh ,

@Misbah is correct in having that as long IF/THEN/ELSE statement is not good. I know I am late to the party, but think about doing the following:

Create a Month List with the code be the number of the month:

Create a SYS Month List Properties module with the following:

*Note - I am adding the "As of " and space after the month here because now the concatenation is only being done 12 times. Not only is this better, but it will lead to less "&" in the final result.

In a SYS Time - Year module (only dimensionalized by Time at the Year Level), have the following line items:

*Note - I am adding the comma and space here because in my model, I only have 2 years so the concatenation is only being done twice.

In a SYS Time - Day module (only dimensionalized by Time at the day Level), have the following line items:

So, you might be asking why am I breaking this up? There are a couple of reasons:

Performance will be much better here because I only have 3 concatenations whereas you had 5 because I have added the spaces, commas, etc to the line item at the SYS level vs in a module that has many other lists. Concatenations are performance killers.
In doing my concatenations on SYS modules, it means they are being done less times and now you can reference the already calculated results from any module that has time or a date.
This now opens it for other model builders to get to the same data whereas if you had your long IF THEN ELSE statement tucked away in a module, not only would performance be poor, but other model builders would not know where to find this logic.

Hope this helps,

Rob

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…