CODEWITHSUNDEEP
excel
user1283776
user1283776

Reputation: 21764

Convert year and ISO-week to date in Excel?

The year is A1=2012 The ISO-week is A2=1

As I understand it the standard way to decide to which month a week belongs is to look at in which month the Thursday occurs.

Thus, I would like to find the date of the Thursday with year A1 and ISO week A2. How can I find the date of the Thursday?

I know that this thread is related, but I can't figure this out: calculate the month from the year and week number in excel

Upvotes: 3

Views: 7353

Answers (2)

OGCJN
OGCJN

Reputation: 393

To find the date defined by (A1,B1,C1) where A1 = ISO Year, B1 = ISO Week, and C1 = the day of the week, assuming week starts with Monday, day 1, use the following:

In VBA:

    S = DateSerial(A1, 1, 3)
    W = Weekday(S)
    D = S - W + 7 * B1 + C1 - 6

Or in one formula in Excel:

=DATE(A1,1,3) - WEEKDAY(DATE(A1,1,3)) + 7 * B1 + C1 - 6

Upvotes: 0

barry houdini
barry houdini

Reputation: 46331

The Thursday of week 1 will always be the first Thursday of the year, then you obviously add 7 for each week after that, so you can get the Thursday date with this formula

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

with Year in A1 and ISO week number in A2

Upvotes: 5

Related Questions