Text Work Week to follow throughout year

[oam]

Member xld helped me out with the formula below and it works well until I get to Work Week 1 like show below but then on week 1 the date does not go to the next year, it stays on the current year.
My question is, how can I adust the formula below so at week 1 it will change to the next year?

Thank you for your help





Work Week Number

51

52

1



Work Week start -Week End

16 Dec 2013 - 20 Dec 2013

23 Dec 2013 - 27 Dec 2013

31 Dec 2012 - 04 Jan 2013






=TEXT(DATE(YEAR(TODAY()),1,1)-WEEKDAY(DATE(YEAR(TODAY()),1,1))+1+(J6-1)*7+1,"dd mmm yyyy")&" - "&TEXT(DATE(YEAR(TODAY()),1,1)-WEEKDAY(DATE(YEAR(TODAY()),1,1))+1+(J6-1)*7+5,"dd mmm yyyy")

[Bob Phillips]

To do that you will need to have Work Year Number in a cell and change the YEAR(TODAY()) in the formula to reference that cell.

[oam]

XLD,
Thank you for quick reply.

Should I replace all the "YEAR(TODAY())" in the formula above to a cell referencing "2013" like the one below? B4 contains “2013” and I6 contains the week number.

Thank you for your help.


=TEXT(DATE($B$4,1,1)-WEEKDAY(DATE($B$4,1,1))+1+(I6-1)*7+1,"dd mmm yyyy")&" - "&TEXT(DATE($B$4,1,1)-WEEKDAY(DATE($B$4,1,1))+1+(I6-1)*7+5,"dd mmm yyyy")

[Bob Phillips]

Yeah, that looks right. Did it work okay?

[oam]

Works Good,

Thank you