Results 1 to 4 of 4

Calculate the number of full weeks (Mon-Sun) between start of quarter and end of quarter

This is a discussion on Calculate the number of full weeks (Mon-Sun) between start of quarter and end of quarter within the Excel Questions forums, part of the Question Forums category; Hi, Seeking additional brain power on this one. I need to calculate the number of weeks between the start of ...

  1. #1
    New Member
    Join Date
    Dec 2013
    Location
    Seattle
    Posts
    49

    Default Calculate the number of full weeks (Mon-Sun) between start of quarter and end of quarter

    Hi,
    Seeking additional brain power on this one. I need to calculate the number of weeks between the start of a quarter and the end of a quarter. Sounds easy right? I need to count only a full week Monday through Sunday. This is where I'm getting brain locked.

    I was using this formula =INT(C2-B2)/7 but it does not take into consideration a full week Monday through to Sunday.
    If you look at Jan, Feb, and Mar on a calendar and count the full weeks, Jan has 4, Feb has 3, Mar has 3 so the total count would be 10 complete weeks Mon-Sun week.

    Here are my quarter start and end dates

    start end
    1/1/2017 3/31/2017
    4/1/2017 6/30/2017
    7/1/2017 9/30/2017
    10/1/2017 12/31/2017

  2. #2
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    607

    Default Re: Calculate the number of full weeks (Mon-Sun) between start of quarter and end of quarter

    Code:
    Excel 2007 32 bit
    
    Start End
    1/1/2017
    3/31/2017
    13
    =(B2-A2)/7
    4/1/2017
    6/30/2017
    13
    7/1/2017
    9/30/2017
    13
    10/1/2017
    12/31/2017
    13
    Paste =B2-A2)/7 in C2 and copy down.

    Adjust formula as required for your cell locations.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,304

    Default Re: Calculate the number of full weeks (Mon-Sun) between start of quarter and end of quarter

    Here are some formulas that may help.

    In D2:
    Code:
    =IF(WEEKDAY(B2,2)=1,B2,B2-WEEKDAY(B2,2)+8)
    Will display the next Monday following the start date, if it's not already a Monday.


    In E2:
    Code:
    =IF(WEEKDAY(C2,2)=7,C2,C2-WEEKDAY(C2,2)+0)
    Will display the previous Sunday from the end date, provided it's not already a Sunday


    In F2:
    Code:
    =(E2-D2+1)/7
    Will give the number of full weeks, Monday through Sunday, with no fractions.


    Or you could put it all together in one big formula:
    Code:
    =(IF(WEEKDAY(C2,2)=7,C2,C2-WEEKDAY(C2,2)+0)-IF(WEEKDAY(B2,2)=1,B2,B2-WEEKDAY(B2,2)+8)+1)/7
    Hope that helps.
    Last edited by Asala42; Apr 21st, 2017 at 03:11 PM.

  4. #4
    New Member
    Join Date
    Dec 2013
    Location
    Seattle
    Posts
    49

    Default Re: Calculate the number of full weeks (Mon-Sun) between start of quarter and end of quarter

    Quote Originally Posted by Asala42 View Post
    Here are some formulas that may help.

    In D2:
    Code:
    =IF(WEEKDAY(B2,2)=1,B2,B2-WEEKDAY(B2,2)+8)
    Will display the next Monday following the start date, if it's not already a Monday.


    In E2:
    Code:
    =IF(WEEKDAY(C2,2)=7,C2,C2-WEEKDAY(C2,2)+0)
    Will display the previous Sunday from the end date, provided it's not already a Sunday


    In F2:
    Code:
    =(E2-D2+1)/7
    Will give the number of full weeks, Monday through Sunday, with no fractions.


    Or you could put it all together in one big formula:
    Code:
    =(IF(WEEKDAY(C2,2)=7,C2,C2-WEEKDAY(C2,2)+0)-IF(WEEKDAY(B2,2)=1,B2,B2-WEEKDAY(B2,2)+8)+1)/7
    Hope that helps.
    Thank you so much for splitting the formula out 'I got it immediately' and it works. I knew there was a way. Thank you!!!!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com