Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

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

  1. #1
    New Member
    Join Date
    Dec 2013
    Location
    Seattle
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    812
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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!!!!

User Tag List

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
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com