Week Number To Date

[danjw_98]

I am trying to convert a week number to a date, either the beginning or ending week date. I tried the below macro but it doesn't work correctly. any help would be appreciated. thanks...

Val2 is the week number to convert to a date. range "d" is the date.

Range("d" & x).Value = "=(val2)+DATE(YEAR(NOW()),1,0)"

 

[Joneye]

May or may not be of use, this is what I use and its automated in another tab.

NOT VB sadly

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="font-weight: bold;;">Week No</td><td style=";">49</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="font-weight: bold;;">Current Date</td><td style=";">01/06/2011</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Financial Calendar</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="font-weight: bold;;">Year End</td><td style=";">03/07/2011</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">FinYear</td><td style=";">Period</td><td style=";">Week No</td><td style=";">Week Start</td><td style=";"></td><td style=";">Week End</td><td style=";"></td><td style="font-weight: bold;;">Remaining Trading Days</td><td style=";">23</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">2011</td><td style=";">1</td><td style=";">1</td><td style=";">28/06/2010</td><td style=";">40357</td><td style=";">04/07/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">2011</td><td style=";">1</td><td style=";">2</td><td style=";">05/07/2010</td><td style=";">40364</td><td style=";">11/07/2010</td><td style=";"></td><td style="font-weight: bold;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">2011</td><td style=";">1</td><td style=";">3</td><td style=";">12/07/2010</td><td style=";">40371</td><td style=";">18/07/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">2011</td><td style=";">1</td><td style=";">4</td><td style=";">19/07/2010</td><td style=";">40378</td><td style=";">25/07/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">2011</td><td style=";">1</td><td style=";">5</td><td style=";">26/07/2010</td><td style=";">40385</td><td style=";">01/08/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">2011</td><td style=";">2</td><td style=";">6</td><td style=";">02/08/2010</td><td style=";">40392</td><td style=";">08/08/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">2011</td><td style=";">2</td><td style=";">7</td><td style=";">09/08/2010</td><td style=";">40399</td><td style=";">15/08/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">2011</td><td style=";">2</td><td style=";">8</td><td style=";">16/08/2010</td><td style=";">40406</td><td style=";">22/08/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">2011</td><td style=";">2</td><td style=";">9</td><td style=";">23/08/2010</td><td style=";">40413</td><td style=";">29/08/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">2011</td><td style=";">3</td><td style=";">10</td><td style=";">30/08/2010</td><td style=";">40420</td><td style=";">05/09/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">2011</td><td style=";">3</td><td style=";">11</td><td style=";">06/09/2010</td><td style=";">40427</td><td style=";">12/09/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">2011</td><td style=";">3</td><td style=";">12</td><td style=";">13/09/2010</td><td style=";">40434</td><td style=";">19/09/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">2011</td><td style=";">3</td><td style=";">13</td><td style=";">20/09/2010</td><td style=";">40441</td><td style=";">26/09/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">2011</td><td style=";">3</td><td style=";">14</td><td style=";">27/09/2010</td><td style=";">40448</td><td style=";">03/10/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">2011</td><td style=";">4</td><td style=";">15</td><td style=";">04/10/2010</td><td style=";">40455</td><td style=";">10/10/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">2011</td><td style=";">4</td><td style=";">16</td><td style=";">11/10/2010</td><td style=";">40462</td><td style=";">17/10/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">2011</td><td style=";">4</td><td style=";">17</td><td style=";">18/10/2010</td><td style=";">40469</td><td style=";">24/10/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">2011</td><td style=";">4</td><td style=";">18</td><td style=";">25/10/2010</td><td style=";">40476</td><td style=";">31/10/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">2011</td><td style=";">5</td><td style=";">19</td><td style=";">01/11/2010</td><td style=";">40483</td><td style=";">07/11/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">2011</td><td style=";">5</td><td style=";">20</td><td style=";">08/11/2010</td><td style=";">40490</td><td style=";">14/11/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style=";">2011</td><td style=";">5</td><td style=";">21</td><td style=";">15/11/2010</td><td style=";">40497</td><td style=";">21/11/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style=";">2011</td><td style=";">5</td><td style=";">22</td><td style=";">22/11/2010</td><td style=";">40504</td><td style=";">28/11/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style=";">2011</td><td style=";">6</td><td style=";">23</td><td style=";">29/11/2010</td><td style=";">40511</td><td style=";">05/12/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style=";">2011</td><td style=";">6</td><td style=";">24</td><td style=";">06/12/2010</td><td style=";">40518</td><td style=";">12/12/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style=";">2011</td><td style=";">6</td><td style=";">25</td><td style=";">13/12/2010</td><td style=";">40525</td><td style=";">19/12/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style=";">2011</td><td style=";">6</td><td style=";">26</td><td style=";">20/12/2010</td><td style=";">40532</td><td style=";">26/12/2010</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style=";">2011</td><td style=";">6</td><td style=";">27</td><td style=";">27/12/2010</td><td style=";">40539</td><td style=";">02/01/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style=";">2011</td><td style=";">7</td><td style=";">28</td><td style=";">03/01/2011</td><td style=";">40546</td><td style=";">09/01/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style=";">2011</td><td style=";">7</td><td style=";">29</td><td style=";">10/01/2011</td><td style=";">40553</td><td style=";">16/01/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style=";">2011</td><td style=";">7</td><td style=";">30</td><td style=";">17/01/2011</td><td style=";">40560</td><td style=";">23/01/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style=";">2011</td><td style=";">7</td><td style=";">31</td><td style=";">24/01/2011</td><td style=";">40567</td><td style=";">30/01/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style=";">2011</td><td style=";">8</td><td style=";">32</td><td style=";">31/01/2011</td><td style=";">40574</td><td style=";">06/02/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style=";">2011</td><td style=";">8</td><td style=";">33</td><td style=";">07/02/2011</td><td style=";">40581</td><td style=";">13/02/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style=";">2011</td><td style=";">8</td><td style=";">34</td><td style=";">14/02/2011</td><td style=";">40588</td><td style=";">20/02/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style=";">2011</td><td style=";">8</td><td style=";">35</td><td style=";">21/02/2011</td><td style=";">40595</td><td style=";">27/02/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style=";">2011</td><td style=";">9</td><td style=";">36</td><td style=";">28/02/2011</td><td style=";">40602</td><td style=";">06/03/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style=";">2011</td><td style=";">9</td><td style=";">37</td><td style=";">07/03/2011</td><td style=";">40609</td><td style=";">13/03/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style=";">2011</td><td style=";">9</td><td style=";">38</td><td style=";">14/03/2011</td><td style=";">40616</td><td style=";">20/03/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style=";">2011</td><td style=";">9</td><td style=";">39</td><td style=";">21/03/2011</td><td style=";">40623</td><td style=";">27/03/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style=";">2011</td><td style=";">9</td><td style=";">40</td><td style=";">28/03/2011</td><td style=";">40630</td><td style=";">03/04/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">45</td><td style=";">2011</td><td style=";">10</td><td style=";">41</td><td style=";">04/04/2011</td><td style=";">40637</td><td style=";">10/04/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">46</td><td style=";">2011</td><td style=";">10</td><td style=";">42</td><td style=";">11/04/2011</td><td style=";">40644</td><td style=";">17/04/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">47</td><td style=";">2011</td><td style=";">10</td><td style=";">43</td><td style=";">18/04/2011</td><td style=";">40651</td><td style=";">24/04/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">48</td><td style=";">2011</td><td style=";">10</td><td style=";">44</td><td style=";">25/04/2011</td><td style=";">40658</td><td style=";">01/05/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">49</td><td style=";">2011</td><td style=";">11</td><td style=";">45</td><td style=";">02/05/2011</td><td style=";">40665</td><td style=";">08/05/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">50</td><td style=";">2011</td><td style=";">11</td><td style=";">46</td><td style=";">09/05/2011</td><td style=";">40672</td><td style=";">15/05/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">51</td><td style=";">2011</td><td style=";">11</td><td style=";">47</td><td style=";">16/05/2011</td><td style=";">40679</td><td style=";">22/05/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">52</td><td style=";">2011</td><td style=";">11</td><td style=";">48</td><td style=";">23/05/2011</td><td style=";">40686</td><td style=";">29/05/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">53</td><td style=";">2011</td><td style=";">12</td><td style=";">49</td><td style=";">30/05/2011</td><td style=";">40693</td><td style=";">05/06/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">54</td><td style=";">2011</td><td style=";">12</td><td style=";">50</td><td style=";">06/06/2011</td><td style=";">40700</td><td style=";">12/06/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">55</td><td style=";">2011</td><td style=";">12</td><td style=";">51</td><td style=";">13/06/2011</td><td style=";">40707</td><td style=";">19/06/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">56</td><td style=";">2011</td><td style=";">12</td><td style=";">52</td><td style=";">20/06/2011</td><td style=";">40714</td><td style=";">26/06/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">57</td><td style=";">2011</td><td style=";">12</td><td style=";">53</td><td style=";">27/06/2011</td><td style=";">40721</td><td style=";">03/07/2011</td><td style=";"></td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:6.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Infromation</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J1</th><td style="text-align:left">=LOOKUP(<font color="Blue">J2,E5:G57,D5:D57</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J2</th><td style="text-align:left">=TODAY(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J3</th><td style="text-align:left">=G57</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J4</th><td style="text-align:left">=LOOKUP(<font color="Blue">J2,E73:E194,F73:F194</font>)</td></tr></tbody></table></td></tr></table><br />

 

[GlennUK]

How about more of clue rather than "it doesn't work correctly"? Although I'd guess that val2 should be val2*7

 

[danjw_98]

sorry about that.

When i run the marco this is what i get in the cell
=(VAL2)+DATE(YEAR(NOW()),1,0)

the val2 should = the cell with the week number in it (ie. c1, etc.)

It should be this
=(c1)+DATE(YEAR(NOW()),1,0)

thanks...

 

[GlennUK]

Well, how about this:

Range("d" & x).Formula = "=(" & val2 & "*7)+DATE(YEAR(NOW()),1,0)"

?
( if I'm understanding you correctly )

 

[danjw_98]

the formula that you provided worked but added 7 days, skewed the dates. I changed the one that I tried with the correct syntax from yours and it seems to work. thanks...


Range("d" & x).Value = "=(" & val2 & ")+DATE(YEAR(NOW()),1,0)"

 

[danjw_98]

ok i take that back after rechecking the one i was using it isn't working the further in time it goes it skews the date.

the other one skews the dates by 7 days, so changed it to the below
Range("d" & x).Formula = "=(" & val2 & "*1)+DATE(YEAR(NOW()),1,0)"

seems to work.. thanks...

 

[GlennUK]

ok i take that back after rechecking the one i was using it isn't working the further in time it goes it skews the date.

the other one skews the dates by 7 days, so changed it to the below
Range("d" & x).Formula = "=(" & val2 & "*1)+DATE(YEAR(NOW()),1,0)"

seems to work.. thanks...

I don't see how, but if you are happy then OK.

 

[Useful]

I am trying to convert a week number to a date, either the beginning or ending week date. I tried the below macro but it doesn't work correctly. any help would be appreciated. thanks...

Val2 is the week number to convert to a date. range "d" is the date.

Range("d" & x).Value = "=(val2)+DATE(YEAR(NOW()),1,0)"

Hi!
=WEEKDAY(NOW()) -?

 

[GlennUK]

Hi!
=WEEKDAY(NOW()) -?

That is not useful. I don't think that you understood the question.