Convert YYYYWW to date

[peter233]

Hi!

Is it possible to convert for example 200801 to a specific date?

Examples:
200801 to become 31/12/2007
200802 to become 07/01/2008
200805 to become 28/01/2008
200810 to become 03/03/2008

[DonkeyOte]

assuming your values are in A1:A4 would the following work for you:

B1: =DATE(LEFT(A1,4),1,1)+((RIGHT(A1,2)-1)*7)+(1-(WEEKDAY(DATE(LEFT(A1,4),1,1),2)))

[martindwilson]

i think you can shorten that to
=DATE(LEFT(A1,4),1,1)+RIGHT(A1,2)*7-8

[Richard Schollar]

This seems to work and is shorter than the Donkey's

=DATE(LEFT(A1,4)+0,1,(RIGHT(A1,2)-1)*7)

or this:

="31/12/2007"+(RIGHT(A1,2)-1)*7

[DonkeyOte]

Yes assuming of course you're only looking at 2008 the one I posted should work with any year week combination assuming OP first date to be returned should be the Monday of the week in which 1st of year appears.