Each time a date changes calculate the number of weeks between it and the next date in the same column

[Yodelayheewho]

Hello Forum!
I have a long column with dates marking the end of each quarter and I would like a formula that will give me the number of weeks within each quarter.
In B2, I have the formula: =IF(A2<>A1,ROUNDDOWN(A2-A1,1)/7,B1). You can see I've run into a couple of issues. First, B2 and B3 results are not what I want and I can't seem to get the results rounded down to a whole number (no decimals). Thank you in advance for your consideration!

	A	B
1	Quarter	Weeks in Quarter
2	12/31/2020	#VALUE!
3	12/31/2020	#VALUE!
4	3/31/2021	12.85714286
5	3/31/2021	12.85714286
6	3/31/2021	12.85714286
7	3/31/2021	12.85714286
8	6/30/2021	13
9	6/30/2021	13
10	9/30/2021	13.14285714
11	9/30/2021	13.14285714
12	12/31/2021	13.14285714

 

[pecoflyer]

Please post a sample sheet ( no pics, they are useless)

 

[Yodelayheewho]

Sure thing! Here it is attached.

 

[vletm]

Yodelayheewho
It's a challenge for Excel to calculate 31-Dec-2020 - Quarter in cell B2. (Quarter is text.)
Delete that formula or use iferror-function.
... as well as if A2 = A1 then why cell B2-value should be text like Weeks in Quarter

 

[pecoflyer]

Enter the following in B2 and pull down =IF(TYPE(A1)=2,"",IF(A2<>A1,ROUNDDOWN((A2-A1)/7;0);B1))

 

[Yodelayheewho]

Hi pecoflyer!
I tried your formula and got an error message. It seems to be pointing to the "7". See attached.

 

[Yodelayheewho]

Hello,
Here are the formulas that are working for me (see attached). In column B, I'm able to calculate the number of WEEKS TO GO for each quarter using the formula: =IF(A2<TODAY(),"",ROUND((DATEDIF(TODAY(),A2,"d")/7),0))
In column G, I'm able to calculate the NUMBER OF WEEKS in each quarter using the formula: =ROUNDDOWN(DATEDIF(E2,F2,"d")/7,0)
Thank you all who responded and provided your insight!

 

[pecoflyer]

Hi pecoflyer!
I tried your formula and got an error message. It seems to be pointing to the "7". See attached.

I forgot to replace the semi colons with a comma ( not everyone uses anglo saxon delimiters)
=IF(TYPE(A1)=2,"",IF(A2<>A1,ROUNDDOWN((A2-A1)/7,0),B1))

 

[vletm]

Yodelayheewho
Delete that B2-cell formula - that's all.

 

[Yodelayheewho]

Updated attached. Now has two sheets. In column B on each sheet are the different formulas. Option 1 is using the formula from vletm and it is working. Unfortunately, the formula from pecoflyer (Option 2) isn't working. As long as I have the one working, I'm happy. Thank you all!