How to use Time Period Format in a Date Formula

PrevContributor
PrevContributor
in Modeling

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?

Tagged:

Best Answer

  • PrevContributor
    PrevContributor
    Answer ✓
    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.

Answers

  • yagna_mahadevan
    yagna_mahadevan
    do you use value function which converts text to number?
  • PrevContributor
    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
    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
    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?

Categories