Weekly or Bi-weekly dates

[ash3154]

Not sure if this can be done with simple formula or will this req.
programming:
Please provide example:

Basically, the Start of week can be any give day (ie Friday, or Sunday, or
Monday).
If the week starts on 2/9/09 (in this case its a Monday), than I would like
to show next week will be
(2/16/09) in cell p10

I would like to keep looping this over and over again, as I would like to
show the new date in cell P10:
so for example if today is Feb 09 2009 (Monday), next week in P10 should be
2/16/09, but if today is 2/17 or 2/19, than the P10 should read 2/23/09.

If someone starts on Friday -- 2/20/09, next week in P10 should read 2/27/09
and than if I open the spreadsheet on 3/10/09 the date should read 3/13/09.

Also, can i use the same formula if its on a bi-weekly basis? (A different
sheet)
Ash

 

[Rick Rothstein]

I think this formula does what you want for the first question (weekly
schedule)...

=TODAY()+(WEEKDAY(TODAY())>=WEEKDAY(O10))*7-WEEKDAY(TODAY())+WEEKDAY(O10)

where I have guessed that O10 contains the "start of the week" date you keep
referring to (put the above formula in P10). I'm not sure off the top of my
head about your second question... check back later to see if I (or anyone
else) have posted a response to you for it.

 

[JBeaucaire]

I think this will do it for you:

=Today()+CHOOSE(WEEKDAY(P9,1),5,4,3,2,1,0,6)

 

[JBeaucaire]

Sorry, this is the correct one for a one week rotation:

=Today()+CHOOSE(WEEKDAY(P9,1),1,2,3,4,5,6,7,8,9)

Then, for biweekly, one of these two should work for you

=IF(ISODD(INT((TODAY()-P9)/7)),
TODAY()+CHOOSE(WEEKDAY(P9,1),1,2,3,4,5,6,7),
TODAY()+CHOOSE(WEEKDAY(P9,1),8,9,10,11,12,13,14))

or

=IF(ISODD(INT((TODAY()-P9)/7)),
TODAY()+CHOOSE(WEEKDAY(P9,1),8,9,10,11,12,13,14),
TODAY()+CHOOSE(WEEKDAY(P9,1),1,2,3,4,5,6,7))

 

[Rick Rothstein]

I don't think that works correctly. Put today's date in A1 and your formula
into B1 (keep the date in P9 as you have it, but make it P$9 because it will
be copied down in a moment). The dates in A1 will represent "today", so
change TODAY() in your formula to A1. Now, copy A1:B1 down. Each line
represents a "today" date and your formula should evaluate to the day of the
week for the date in P9. Unless I have done something wrong, I don't get
dates with the same day of the week as the date in P9 using your formula.