Date Conversion

PrevContributor
PrevContributor
in Modeling

Hi  I am looking for a forumula that converts the year and week number in to a date format Example - 2015.01 converted to 1.01.2015 What could be the formula for this Thanks

Tagged:

Best Answer

  • peter_mcanena
    peter_mcanena
    Answer ✓
    Hi Mansour,

    The simplest implementation of that conversion would look something like this: Assuming your imported data is in a text-formatted line item called Week Code, you need a new date-formatted line item with the following formula:
    DATE(VALUE(LEFT(Week Code, 4)), 1, 1) + 7 * (VALUE(RIGHT(Week Code, 2)) - 1)
    This extracts a year number from the first four characters and a week number from the last two characters, then takes the first of January of the relevant year and adds the appropriate number of weeks.

    That's in line with your description of the requirement. If you want to implement a standard week number algorithm (such as https://en.wikipedia.org/wiki/ISO_week_date) then you'll need a few intermediate steps.

Answers

  • michel_maurel
    michel_maurel
    Hi,
    you can use a mix of following Anaplan functions : 

    Date(Year, month, day)
    Start() to get the first day of the week.

    To be more accrate in my answer, I need to knwo wheter you are using week as a timescale or as an imported data.

    Kind regards.
    Michel.
  • PrevContributor
    PrevContributor
    [quote]Michel MaurelHi,
    you can use a mix of following Anaplan functions : 

    Date(Year, month, day)
    Start() to get the first day of the week.

    To be more accrate in my answer, I need to knwo wheter you are using week as a timescale or as an imported data.

    Kind regards.
    Michel.[/quote]
    Hi

    Many thanks for your reply.

    So i have a coumn in my module which has imported data which is in the format 2015.01 i.e week 1 in the year 2015
    What i would like to do is convert that to 01/01/2015 and convert week 2 to 08/01/20015

    Appreciate if ypou could please help with this formula.

    thanks
  • PrevContributor
    PrevContributor
    [quote]Peter McAnenaHi Mansour,

    The simplest implementation of that conversion would look something like this: Assuming your imported data is in a text-formatted line item called Week Code, you need a new date-formatted line item with the following formula:
    DATE(VALUE(LEFT(Week Code, 4)), 1, 1) + 7 * (VALUE(RIGHT(Week Code, 2)) - 1)
    This extracts a year number from the first four characters and a week number from the last two characters, then takes the first of January of the relevant year and adds the appropriate number of weeks.

    That's in line with your description of the requirement. If you want to implement a standard week number algorithm (such as https://en.wikipedia.org/wiki/ISO_week_date) then you'll need a few intermediate steps.[/quote]
    Good Morning Peter,

    Thank you very very much
    This works abosilutely perfect.

    Thanks once again. Exactly what i was looking for.

    Have a Great day ahead and thank you very much once again. :-)

    Regards

    Mansour

Categories