Year + week => first date of week

[bash]

Hi,

I would like Excel to calculate the date for the first day (Monday) in
a week.I have my sheet setup like this:
Cell = A1 = Year = 2009
Cell = A2 = Week = 32

Cell = A3 =DATE(A3,1,1)+7*B3 this gives me the Excel date = 40038
(2009-08-13 Thursday) this is not correct.

Correct Excel date for 2009 week 32 should be 40028 (2008-08-03
Monday).

I live in Sweden and we use the ISO week that starts on Mondays (first
week of the new year is the first week that contains Thursday). I
don't know if it has got something to do with that the formula above
does not work.

Above formula is the one I have found on the internet that people have
used to solve my question. But does anyone know why it does not work
for me? Does anyone know how to correct it?

Many thanks in advance
/Daniel

 

[Pete_UK]

Try this:

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

Year in A1 and week in A2.

Hope this helps.

Pete

 

[Ron Rosenfeld]

Hi,

I would like Excel to calculate the date for the first day (Monday) in
a week.I have my sheet setup like this:
Cell = A1 = Year = 2009
Cell = A2 = Week = 32

Cell = A3 =DATE(A3,1,1)+7*B3 this gives me the Excel date = 40038
(2009-08-13 Thursday) this is not correct.

Correct Excel date for 2009 week 32 should be 40028 (2008-08-03
Monday).

I live in Sweden and we use the ISO week that starts on Mondays (first
week of the new year is the first week that contains Thursday). I
don't know if it has got something to do with that the formula above
does not work.

Above formula is the one I have found on the internet that people have
used to solve my question. But does anyone know why it does not work
for me? Does anyone know how to correct it?

Many thanks in advance
/Daniel

Yes, the ISO week is the issue. Your formula assumes week 1 starts on Jan 1.

Try this formula:

=DATE(A1,1,8)-WEEKDAY(DATE(A1,1,8-2))-7*(DAY(
DATE(A1,1,8)-WEEKDAY(DATE(A1,1,8-2)))>=5)+7*(A2-1)

--ron

 

[bash]

Try this:

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

Year in A1 and week in A2.

Hope this helps.

Pete

Workes! Thank you!

 

[bash]

Yes, the ISO week is the issue.  Your formula assumes week 1 starts on Jan 1.

Try this formula:

=DATE(A1,1,8)-WEEKDAY(DATE(A1,1,8-2))-7*(DAY(
DATE(A1,1,8)-WEEKDAY(DATE(A1,1,8-2)))>=5)+7*(A2-1)

--ron

Hi,

Thanks. Also works!

/Daniel

 

[Ron Rosenfeld]

Hi,

Thanks. Also works!

/Daniel

You're welcome. Glad to help.

Did I misunderstand something, though?

I thought you always wanted the Monday to be returned. Mine should always
return a Monday, but Pete's does not, depending on the year being used.
--ron

 

[Ron Rosenfeld]

Try this:

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

Year in A1 and week in A2.

Hope this helps.

Pete

Maybe I misunderstood something.

Your routine gives me unexpected results for years other than 2009.

For example, try 2010 and 1
Yours --> Wed Dec 30 2009 (ISO week 53)
Mine --> Mon Jan 04 2010 (ISO week 1
--ron

 

[bash]

Maybe I misunderstood something.

Your routine gives me unexpected results for years other than 2009.

For example, try 2010 and 1
        Yours --> Wed Dec 30 2009    (ISO week 53)
        Mine -->  Mon Jan 04 2010    (ISO week 1
--ron

Hi Ron,

You are one step ahead of me. Thanks for checking other years than
2009. Your assumption that I only want Monday's is correct.

Formula in full:
=DATE(A1,1,8)-WEEKDAY(DATE(A1,1,8-2))-7*(DAY(DATE(A1,1,8)-WEEKDAY(DATE
(A1,1,8-2)))>=5)+7*(A2-1)

I tried to split your formula into parts to make it easier to
understand. But sometimes a short text explaining the formula is
easier...

Many thanks in advance.

/Daniel

 

[Ron Rosenfeld]

Hi Ron,

You are one step ahead of me. Thanks for checking other years than
2009. Your assumption that I only want Monday's is correct.

Formula in full:
=DATE(A1,1,8)-WEEKDAY(DATE(A1,1,8-2))-7*(DAY(DATE(A1,1,8)-WEEKDAY(DATE
(A1,1,8-2)))>=5)+7*(A2-1)

I tried to split your formula into parts to make it easier to
understand. But sometimes a short text explaining the formula is
easier...

Many thanks in advance.

/Daniel

First Monday of the year
=DATE(A1,1,8)-WEEKDAY(DATE(A1,1,8-2))

Is first Monday of the year on or after the 5th of the year?
IF so, subtract 7 days to get to the first day of week 1
-7*(DAY(DATE(A1,1,8)-WEEKDAY(DATE(A1,1,8-2)))>=5)\

Then add seven days for each week (adjust to zero-based count) to get to
desired date
+7*(A2-1)
--ron

 

[bash]

First Monday of the year
=DATE(A1,1,8)-WEEKDAY(DATE(A1,1,8-2))

Is first Monday of the year on or after the 5th of the year?
IF so, subtract 7 days to get to the first day of week 1
-7*(DAY(DATE(A1,1,8)-WEEKDAY(DATE(A1,1,8-2)))>=5)\

Then add seven days for each week (adjust to zero-based count) to get to
desired date
+7*(A2-1)
--ron

Thanks Ron,

Now I understand.

I can't really get why Excel does not have a inbuilt formula for what
you have created.

/Daniel