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

Accepted answers

peter_mcanena

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)

All comments

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

[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

peter_mcanena

DATE(VALUE(LEFT(Week Code, 4)), 1, 1) + 7 * (VALUE(RIGHT(Week Code, 2)) - 1)

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

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.…