Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weekly or Bi-weekly dates
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weekly or Bi-weekly dates
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. -- Rick (MVP - Excel) "ash3154" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weekly or Bi-weekly dates
I think this will do it for you:
=Today()+CHOOSE(WEEKDAY(P9,1),5,4,3,2,1,0,6) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "ash3154" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weekly or Bi-weekly dates
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)) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: I think this will do it for you: =Today()+CHOOSE(WEEKDAY(P9,1),1,2,3,4,5,6,7,8,9) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "ash3154" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weekly or Bi-weekly dates
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. -- Rick (MVP - Excel) "JBeaucaire" wrote in message ... 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)) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: I think this will do it for you: =Today()+CHOOSE(WEEKDAY(P9,1),1,2,3,4,5,6,7,8,9) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "ash3154" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weekly or Bi-Weekly dates | Excel Programming | |||
Chart displaying weekly data group in months without weekly labels | Charts and Charting in Excel | |||
with weekly score sheet how do I column a weekly progressive aver. | Excel Worksheet Functions | |||
populating a list box with weekly dates | Excel Worksheet Functions | |||
Calculating weekly avegage efficiencies on a weekly basis | Excel Programming |