5 week process

[Exel2]

I have a workbook used for data management, which operates over 5 weeks.
What I am trying to do is indicate which week is being worked on.
For instance if week one commenced on 5/8/2012, then in cell A1 it would say (W/C and (MM/DD/YY)). Then on 12/8/12 it would change to reflect that date. I used this formula for this process ((TODAY())-WEEKDAY((TODAY()),1)+1) so that every week the week commencing would change, and this works fine. It's the next part that baffles me.

In cell A2 I want it to say "Week 1" when A1 says 5/8/2012, and "Week 2" when A1 says 12/8/12 and so on until when cell A1 reads 2/9/12 cell A2 reads "Week 5". However, upon A1 reading 9/9/12 (week 6) cell A2 should read "Week 1" again until week 5 with this cycle repeating itself perpetually.

Please help as I've tried lots of different combinations to no avail.

[oeldere]

maybe

=

int(((TODAY())-WEEKDAY((TODAY()),1)+1)/5)

[daddylonglegs]

Try this formula

="Week "&MOD(INT((TODAY()-DATE(2012,8,5))/7),5)+1

If you want to test that then replace TODAY() in the formula with A1 (or any cell) and put various dates in that cell to see what result you get, next week will be week 5 then it reverts to week 1 on September 9th

[Exel2]

That's amazing exactly what I was looking for! You make it seem so simple. Honestly I was using if this and if that.

[Exel2]

Hey, thank you for your help. I have one more question. Say I had 5 sheets with info from a specific week, say "wk 1", "wk 2","wk 3","wk 4","wk 5". Now week 1 begins say 5/8/2012. I need cell A1 which contains the WC date to update the date only in the cycle pertaining to the date for week 1 one. Equally, the other sheets must contain the corresponding dates and update accordingly.

Thank you so much

[daddylonglegs]

OK, so if it's week 1 you want A1 in sheet wk 1 to show the start date of that week.....but when it's week 2 (or 3 or 4) what should it show then, do you still want it to show the week 1 start date (or something else)?

[Exel2]

i only want the sheet containing week 1 data to hold the week 1 commencing date, in wk2,3,4,and5 the dates for those weeks respectively. So in sheet 1 ("week1) A1 will have the week 1 date, in sheet 2 the week2 date and so on. when the weeks progress the date in week 1 will not change until the 5 week cycle reaches it. Equally, sheet 2 ("week2") will not hold any dates besides those in the cycle as week 2. ie.current date is say,5/8/2012 then sht1,A1=5/8/2012, sht1,A2=week1. Then current date is 12/8/2012 then sht1,A1=9/9/2012, sht1,A2=week1.

All other sheets working the same for their respective dates.

I tried this but I'm not confident in how will work for future dates. =IF($B$2=ROUNDUP((MOD(ROUNDDOWN(NOW(),0)+5,35)+1)/7,0),ROUNDDOWN(NOW(),0)-1-(WEEKDAY(ROUNDDOWN(NOW(),0)-1)-2),IF($B$2=(MOD(ROUNDUP((MOD(ROUNDDOWN(NOW(),0)+5,35)+1)/7,0),1)+1),ROUNDDOWN(NOW(),0)+6-(WEEKDAY(ROUNDDOWN(NOW(),0)+6)-2),IF($B$2=(MOD(ROUNDUP((MOD(ROUNDDOWN(NOW(),0)+5,35)+1)/7,0),2)+2),ROUNDDOWN(NOW(),0)+20-(WEEKDAY(ROUNDDOWN(NOW(),0)+20)-2),IF($B$2=(MOD(ROUNDUP((MOD(ROUNDDOWN(NOW(),0)+5,35)+1)/7,0),3)+2),ROUNDDOWN(NOW(),0)+27-(WEEKDAY(ROUNDDOWN(NOW(),0)+27)-2),ROUNDDOWN(NOW(),0)+13-(WEEKDAY(ROUNDDOWN(NOW(),0)+13)-2)))))

[daddylonglegs]

Try using this formula in Week 1 sheet

=TODAY()-MOD(TODAY()-DATE(2012,8,5),35)

and just add 7 days to the date in each of the other sheets, i.e. in Week 2

=TODAY()-MOD(TODAY()-DATE(2012,8,12),35)

....although ideally you need a date in the past so week 5 should be

=TODAY()-MOD(TODAY()-DATE(2012,7,29),35)

[Exel2]

*****THANK YOU*****

Any input on what was wrong with my method?