How to use Time Period Format in a Date Formula

The date formula requires you to use numbers for the years, months, and days. I have 2 line items in a module that have the year, and a custom month list that contains the members of Jan, feb, mar, etc... For the month, I have a property on that list with the month number, so I can easily pull that into the date formula, but year cannot be pulled in. Trying to use year directly gets: "The 'year' argument for the 'DATE' function should have datatype of NUMBER not TIME PERIOD: DATE(year:NUMBER, month:NUMBER, day:NUMBER)" I can't convert it to text or a value or anything, so there is I don't see a way to populate the current period using a line item with a time period format. Has anyone run across this yet?

Accepted answers

PrevContributor

Sorry for the late reply.

You can use the formula YEAR() to convert a Year-formatted line item to a number, and use it in a DATE()-formula. For example DATE(YEAR("year formatted line item"), 1, 1) gives 20xx-01-01

But I agree that you shouldn't have to convert it. It would be easier if DATE() took both numbers and periods. You should write a request for enhancement about it.

All comments

yagna_mahadevan

do you use value function which converts text to number?

PrevContributor

It isn't "text", it is essentially a list item. I haven't tried, but I wonder if Value(name()) would work since the name would return the text version of the year, then value could convert it to a number.

Either way, it seems like a lot of hoops to jump through to use a year formatted list item as a year in a date formula.

PrevContributor

Why aren't you using just one line item with year and month time period? I agree that it is a hassle to do what you want to do. But Im not sure I understand why you would want to write/select year and then write/select month to create a date. Time period would do all that for you.

If you could give some more information about what you are doing with the date formula, maybe we could help out.

/David

PrevContributor

It is mostly with data integration, and areas that I use custom date dimensions to get graphs to look halfway decent.

With Data integration, month and year are separate dimensions coming in from other source systems. I have them mapped into those custom date dimensions for graphing purposes, but then I have to do all of these conversions just to get it into a date format.

It is just something that doesn't make sense to me. Why would you add list items for year and month, and not allow them to be used in a date formula?

PrevContributor

Quick Links

Can you help?

Unanswered questions

Sorting General List
Hi, I wonder if there's a way I can sort my general list alphabetically. The list is used as a drop down menu in a line item module and I want the drop down menu to be easy to navigate. I've already tried the Order List action. I already have a module with a text-formatted line item based on that list. But when I try to…
Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
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.…