number of weeks into the quarter

[dell_dude]

i'm trying to create a formula which calculates number of weeks into the quarter.

for example, the quarter starts
1/26/2011 Q1
4/26/2011 Q2
7/26/2011 Q3
10/26/2011 Q4

and based on today's date, it would tell me how many weeks i'm into the Q. (each Q is 13 weeks)
ex. today is 9/20/2011 (i'm 8.14 weeks into this quarter).

does anyone know the formula for this without using macros.
Thanks,

 

[Dannyh1]

Hi,

based on below scenario, I get exactly 8 weeks (56 days between 9/20/2011 & 26/7/2011)

Assuming Dates & Quaters are in Col A & B & Todays date is in cell C1.

Maybe try:

=ROUND((C1-VLOOKUP(C1,A:B,1))/7,2)&" Weeks into "&VLOOKUP(C1,A:B,2)

 

[dell_dude]

Dannyh1,

thanks for your quick reply. i think i figured it out using =if & =and & =date statements. i really don't want to use vlookup because this is only part of the formula..

Thanks again,

 

[dell_dude]

Hi,

based on below scenario, I get exactly 8 weeks (56 days between 9/20/2011 & 26/7/2011)

Assuming Dates & Quaters are in Col A & B & Todays date is in cell C1.

Maybe try:

=ROUND((C1-VLOOKUP(C1,A:B,1))/7,2)&" Weeks into "&VLOOKUP(C1,A:B,2)

for some reason, i'm having problems with dates that range from 1/1/2011 - 1/24/2011.