How to calculate the number of weeks between 2 date range in excel

I have a question here, I have 3 columns in excel, A1 is being the calendar date; column B1 has the fiscal_quarter_start_date ; and fiscal_quarter_end_date in column C1.

I am trying to find the number of week falling in between the A2 and A3. The issue here is I should consider the first sunday as the first week of the quarter. My fiscal year starts on Feb of every month.

The reason I am doing this calculation is to populate the count of weeks numbers of a quarter, where my first week starts from the first sunday of the quarter.

=ROUNDUP(MOD(IF(A13>=IF(DATE(YEAR($A13),2,1)=1,DATE(YEAR($A13),2,1),DATE(YEAR(A13),2,7-WEEKDAY(DATE(YEAR(A13),2,1),1)+2)),ROUNDUP((A13-IF(WEEKDAY(DATE(YEAR(A13),2,1),1)=1,DATE(YEAR($A13),2,1),DATE(YEAR($A13),2,7-WEEKDAY(DATE(YEAR($A13),2,1),1)+2))+1)/7,0),ROUNDUP((A13-IF(DATE(YEAR($A13)-1,2,1)=1,DATE(YEAR($A13)-1,2,1),DATE(YEAR($A13)-1,2,7-WEEKDAY(DATE(YEAR($A13)-1,2,1),1)+2))+1)/7,0)),13.01),0) 

above is the formula i've got all the way. However rounding up with 13 is not a viable solution as its not necessary that it should have always have 13 weeks all the time.

For example: 5/1/16 should be week 1 As it is a begging of a q2 and 5/1/16 is the starting on Sunday. In this case using above formula result for the week is 13

Please help me..

4 Answers

You can use WEEKNUM to get the week number.

However you mentioned that your week wont end at the Saturday, but at the Sunday, so you have to do =WEEKNUM(<insert here where the date is>,2)

=WEEKNUM (serial_num, [return_type]) 

• serial_num - A valid Excel date in serial number format.
• return_type - [optional] The day the week begins. Default is 1.

Return_type can either be 1 or 2:

• 1 - Week begins on Sunday
• 2 - Week begins on Monday

or... read it here for counting weeks between 2 dates.

1. Enter this formula =(A2-A1)/7 into a blank cell, (A2 indicates the end date and A1 is the start date)
2. If you just want to get the whole weeks, please apply this INT function: =INT((A2-A1)/7)

Try it here, on sheet "week"

Hope it'll help.

Assuming that the Quarter start and end dates are in A3:A6 respectively then:

=IF(AND(C1>=$A3,C1<$A4),WEEKNUM(C1)-WEEKNUM($A3)+1,IF(AND(C1>=$A4,C1<$A5),WEEKNUM(C1)-WEEKNUM($A4)+1,IF(AND(C1>=$A5,C1<$A6),WEEKNUM(C1)-WEEKNUM($A5)+1,WEEKNUM(C1)-WEEKNUM($A6)+(IF(AND(C1>=DATE(YEAR(C1),1,1),C1<DATE(YEAR(C1),MONTH($A6),DAY($A6))),53,1))))) 

This is checking whether the date in the cell above (C1) is between the quarter start and end dates, and checking the difference in weeks from the start date. To cover the anomoly for Q4 extending into the next year, the IF statement at the end adds 53 instead of 1 for the affected dates.

Edit #1

=WEEKNUM(A1)-WEEKNUM(B1)+IF(AND(A1>=DATE(YEAR(A1),1,1),A1<DATE(YEAR(A1),MONTH(B1),DAY(B1))),53,1) 

This works for the A1:C1 format you mentioned in your comment. Obviously this relies on B1 and C1 being updated manually.

Edit #2

=WEEKNUM(C1)-WEEKNUM(B1) 

If you are counting 05/01/2016 as week 1 then add 1 to the result. Take off the highest week number (default is counting from sunday or you can change to (C1,1) if you want to define it exactly) from the lowest week number. Reult is 13 which would match the manual calculation.

You will want to look at the previous edit for an example of the if formula you will need when the quarter ends in another year. to reverse the negative figure it will need to add 52 when the date is in the early part of the next year and 0 when it is not.

You can use the "hidden" function DATEDIF:

For example in A1 we write start date (01/05/2016), in B1 - end date (31/07/2016) and in C1 = DATEDIF(A1,B1,"d")/7 the result will be 13