CODEWITHSUNDEEP
exceldate-conversion
Christiane S.
Christiane S.

Reputation: 1

Excel: Convert calendar week (format ww.yyyy) into date

I need to convert an Excel calendar week date into the actual date.

Excel calendar week format = ww.yyyy (e.g. 31.2014); Expected output = 7/28/2014 (return the Monday of the week)

What formula should I use ?

Upvotes: 0

Views: 11297

Answers (2)

Jonathan Wheeler
Jonathan Wheeler

Reputation: 2699

If @Bathsheba's response works for you, you can do everything in cell B1 with the following command

=MAX(DATE(LEFT(B1,FIND(".",B1)),1,1),DATE(LEFT(B1,FIND(".",B1)),1,1)-WEEKDAY(DATE(LEFT(B1,FIND(".",B1)),1,1),2)+(MID(B1,FIND(".",B1)+1,2)-1)*7+1)

This allows you to put YYYY.WW in B1 and it splits it up for you in the calculation.

Upvotes: 2

Bathsheba
Bathsheba

Reputation: 234865

It's a bit of a pain: there's no direct function. If A1 contains the year, and A2 contains the week number then,

=MAX(DATE(A1,1,1),DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1),2)+(A2-1)*7+1)

will return the date corresponding to the Monday of that week in that year.

To test it, use =WEEKNUM() and =YEAR() on the computed result, along with =TEXT(,"DDD") to prove it's a Monday.

Upvotes: 3

Related Questions