Converting time format

[a94andwi]

Hello.

I have a range containing dates in this format:
YYYYWWD
2010021 or
Year 2010 week 2 day 1

I would like to convert this to new date format 2010-01-11 (11th of january).

How can this be done? Any ideas?

/Anders

[DonkeyOte]

If we assume that Week 1 commences from first Mon of year then perhaps:

=DATE(IF(ISNUMBER($A1);LEFT($A1;4);MID($A1;6;4));1;1)-(WEEKDAY(IF(ISNUMBER($A1);LEFT(A1;4);MID(A1;6;4))&"-01-01";3)-7)+7*(MID($A1;IF(ISNUMBER($A1);5;16);2)-1)+RIGHT($A1)-1

where A1 holds either the number or text string

[JeanRage]

Hi,

Among many possibilities ...
see attached file

HTH

[gutteg]

Even if this is an old thread I post my solution.
I adapted other solutions to suit my needs, perhaps it will be of use for somebody else.

With ; as separator in the formula:
=DATE(LEFT(A1;4);1;0)+((MID(A1;5;2)-1)*7)+RIGHT(A1;1)-VLOOKUP(LEFT(A1;4)+0;Sheet2!$A$1:$B$10;2;FALSE)
With , as separator:
=DATE(LEFT(A1;4);1;0)+((MID(A1;5;2)-1)*7)+RIGHT(A1,1)-VLOOKUP(LEFT(A1,4)+0,Sheet2!$A$1:$B$10,2,FALSE)

The formula makes a date of the YYYY (ie 2010-01-00 = 2009-12-31) and then adds WW-1*7 and adds D. Finally it looks up and subtract the number of days in W1 belonging to previous year listed in sheet2 as follow (a negative number is no days in current year belonging to w52 last year). The +0 is to make sure that the year is in number format and not text.
SHEET2:
2005 -2
2006 -1
2007 0
2008 1
2009 3
2010 -3
2011 -2
2012 -1
2013 1
2014 2
2015 3