oam
11-12-2013, 08:18 PM
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")
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")