Date Conversion Previous Contributor

Date Conversion

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

1 ACCEPTED SOLUTION

Accepted Solutions  Regular Contributor

RE: Date Conversion

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.
4 REPLIES 4  Regular Contributor

RE: Date Conversion

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. Previous Contributor

RE: Date Conversion

[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  Regular Contributor

RE: Date Conversion

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. Previous Contributor

RE: Date Conversion

[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