Year + week => first date of week

  • Thread starter Thread starter bash
  • Start date Start date
B

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
 
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
 
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
Click to expand...

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
 
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
Click to expand...

Hi,

Thanks. Also works!

/Daniel
 
Hi,

Thanks. Also works!

/Daniel
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...


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
 
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
Click to expand...

Thanks Ron,

Now I understand.

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

/Daniel
 
Back
Top