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
Solved! Go to Solution.
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.
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.
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