+ Reply to Thread
Results 1 to 33 of 33

Count full weeks Monday thru Sunday between 2 dates

  1. #1
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Count full weeks Monday thru Sunday between 2 dates

    Another thread that wanted to count "weekends" got me to thinking about this one:

    Count the full weeks (from Monday thru Sunday) between 2 dates.

    For example...

    Data Range
    A
    B
    C
    D
    E
    F
    1
    Date
    Weekday
    ------
    Start
    End
    Weeks
    2
    8/1/2013
    Thu
    8/1/2013
    8/15/2013
    1
    3
    8/2/2013
    Fri
    4
    8/3/2013
    Sat
    5
    8/4/2013
    Sun
    6
    8/5/2013
    Mon
    7
    8/6/2013
    Tue
    8
    8/7/2013
    Wed
    9
    8/8/2013
    Thu
    10
    8/9/2013
    Fri
    11
    8/10/2013
    Sat
    12
    8/11/2013
    Sun
    13
    8/12/2013
    Mon
    14
    8/13/2013
    Tue
    15
    8/14/2013
    Wed
    16
    8/15/2013
    Thu


    Full Monday thru Sunday weeks = 1

    This array formula** entered in F2:

    =(MAX(IF(WEEKDAY(ROW(INDIRECT(D2&":"&E2)),2)=7,ROW(INDIRECT(D2&":"&E2))))-MIN(IF(WEEKDAY(ROW(INDIRECT(D2&":"&E2)),2)=1,ROW(INDIRECT(D2&":"&E2))))+1)/7

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    The logic of the formula is:

    The max Sunday date within the date range minus the min Monday date within the date range +1 = total days divided by 7 (days in a full week) = full weeks

    I'm thinking there has to be a nice short formula that will do this.

    daddylonglegs, you out there?
    Last edited by Tony Valko; 07-16-2014 at 03:41 PM. Reason: added the missing words !
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  2. #2
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Count full weeks Monday thru Sunday between 2 dates

    Shouldn't this work?

    =WEEKNUM(E2,2)-WEEKNUM(D2,2)

  3. #3
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Count full weeks Monday thru Sunday between 2 dates

    Quote Originally Posted by hoyasaxa215 View Post
    Shouldn't this work?

    =WEEKNUM(E2,2)-WEEKNUM(D2,2)
    No. If the start date was 11 Aug 14 and the end date was 12 Aug 14 your formula would return a 1 as they are different WEEKNUM's, but there are 0 full weeks in that period.
    Please add reputation by clicking on the * if I have helped.
    Please mark the thread SOLVED if your issue has been resolved.
    Thanks, Glenn.

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count full weeks Monday thru Sunday between 2 dates

    Quote Originally Posted by hoyasaxa215 View Post
    Shouldn't this work?

    =WEEKNUM(E2,2)-WEEKNUM(D2,2)
    The date range can span more than the same year.

    Start date: 1/1/2000
    End date: 1/1/2014

    The correct result is 730 while your formula returns 0.

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Count full weeks Monday thru Sunday between 2 dates

    this will give the same results

    D
    E
    F
    G
    1
    Start Date End Date =INT(DATEDIF(D2,E2,"D")/7) =INT(NETWORKDAYS.INTL(D2,E2,1)/5)
    2
    1/1/2000
    1/1/2014
    730
    730
    Last edited by AlKey; 07-16-2014 at 07:04 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  6. #6
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Count full weeks Monday thru Sunday between 2 dates

    Quote Originally Posted by AlKey View Post
    this will give the same results

    D
    E
    F
    G
    1
    Start Date End Date =INT(DATEDIF(D2,E2,"D")/7) =INT(NETWORKDAYS.INTL(D2,E2,1)/5)
    2
    1/1/2000
    1/1/2014
    730
    730
    Try your formulas with a start date of 9 July 2014 and an end date of today (17 July 2014). They both return 1. There is however 0 full Monday to Sunday weeks in that 8 day period.

  7. #7
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Count full weeks Monday thru Sunday between 2 dates

    Maybe this?
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by gak67; 07-16-2014 at 07:19 PM. Reason: Changed references to match OP

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count full weeks Monday thru Sunday between 2 dates

    @AlKey

    Both of those are off by 1 on many date combinations:

    1/25/2003 - 8/27/2005
    6/22/2001 - 8/27/2005
    4/13/2007 - 1/12/2008

    The NETWORKDAYS.INTL version doesn't always return the same result as the DATEDIF version:

    4/13/2007 - 12/27/2007
    11/24/2000 - 12/27/2007
    2/12/2009 - 9/19/2012

  9. #9
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Count full weeks Monday thru Sunday between 2 dates

    Or just slightly shorter:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count full weeks Monday thru Sunday between 2 dates

    Quote Originally Posted by gak67 View Post
    Maybe this?

    =IFERROR(INT(DATEDIF(IF(WEEKDAY(D2,2)=1,D2,D2+8-WEEKDAY(D2,2)),IF(WEEKDAY(E2,2)=7,E2,E2-WEEKDAY(E2,2)),"D")/7)+1,0)
    Ok, that works!

    We're making progress!

    I'm thinking that daddylonglegs, the undisputed date formula master, has something a bit more compact.

  11. #11
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count full weeks Monday thru Sunday between 2 dates

    Quote Originally Posted by gak67 View Post
    Or just slightly shorter:=INT((IF(WEEKDAY(E2,2)=7,E2,E2-WEEKDAY(E2,2))-IF(WEEKDAY(D2,2)=1,D2,D2+8-WEEKDAY(D2,2)))/7)+1
    Sweet!

    Now comes the hard part...

    Explain the logic of how it works.

  12. #12
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Count full weeks Monday thru Sunday between 2 dates

    Tony, with minus one day it seems like it returns correct number of days. I've tried on few pair days and compared with formulas above.


    D
    E
    F
    G
    H
    I
    J
    1
    Start Date End Date =DATEDIF(D2,E2,"D")/7-1 =NETWORKDAYS.INTL(D2,E2,1)/5-1 gak67 1 gak67 2 Tony 1
    2
    1/1/2000
    1/1/2014
    730
    730
    730
    730
    730
    3
    1/25/2003
    8/27/2005
    134
    134
    134
    134
    134
    4
    6/22/2001
    8/27/2005
    217
    217
    217
    217
    217
    5
    4/13/2007
    1/12/2008
    38
    38
    38
    38
    38
    6
    4/13/2007
    12/27/2007
    36
    36
    36
    36
    36
    7
    11/24/2000
    12/27/2007
    369
    369
    369
    369
    369
    8
    2/12/2009
    9/19/2012
    187
    187
    187
    187
    187

  13. #13
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Count full weeks Monday thru Sunday between 2 dates

    Ok. It moves the end date to the previous Sunday (if its not already a Sunday). That's the IF(WEEKDAY(E2,2)=7,E2,E2-WEEKDAY(E2,2)) part. It also moves the start dart to the following Monday (if it's not already a Monday). That's the IF(WEEKDAY(D2,2)=1,D2,D2+8-WEEKDAY(D2,2)) part. It subtracts these two dates from each other giving a number of days between them and divides it by 7. It takes the whole number part of that (the INT function) and adds 1 to that to ensure the first (or last - depending on how you look at it) week is also included.

    It will return a negative number if the start date is after the end date, but that's illogical so it shouldn't be an issue.

  14. #14
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Count full weeks Monday thru Sunday between 2 dates

    Quote Originally Posted by AlKey View Post
    Tony, with minus one day it seems like it returns correct number of days. I've tried on few pair days and compared with formulas above.
    Try it with a start date of a Monday (say 30 Jun 2014) and and end date of a Sunday (say 13 Jul 2014). Your DATEDIF formula returns 0 and your NETWORKDAYS.INTL formula returns 1. The answer should be 2.

  15. #15
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count full weeks Monday thru Sunday between 2 dates

    Quote Originally Posted by gak67 View Post
    Ok. It moves the end date to the previous Sunday...
    Makes perfect sense.

    What are you gonna do with all the rep I'm giving you?

  16. #16
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Count full weeks Monday thru Sunday between 2 dates

    maybe just finding the from start date the next monday (or same day if it is a monday) and from end date the previous sunday(or same day if it is a sunday) subtract the 2 add 1 and then / by 7
    =((E2-WEEKDAY(E2)+1)-(D2-WEEKDAY(D2-2)+7)+1)/7
    Last edited by martindwilson; 07-16-2014 at 08:58 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  17. #17
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Count full weeks Monday thru Sunday between 2 dates

    Quote Originally Posted by Tony Valko View Post
    What are you gonna do with all the rep I'm giving you?
    Dunno. What can I do with it? Is it like a loyalty scheme where you can redeem the points for prizes?

  18. #18
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count full weeks Monday thru Sunday between 2 dates

    Quote Originally Posted by gak67 View Post
    Is it like a loyalty scheme where you can redeem the points for prizes?
    That's an excellent idea.

  19. #19
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count full weeks Monday thru Sunday between 2 dates

    Quote Originally Posted by martindwilson View Post
    =((E2-WEEKDAY(E2)+1)-(D2-WEEKDAY(D2-2)+7)+1)/7
    Even sweeter!

    I think that's about as compact a formula as we can expect for this task.

  20. #20
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Count full weeks Monday thru Sunday between 2 dates

    So my logic was right, but I didn't have the most efficient way to achieve it. I can work with that. If you're after compact you don't need all the parentheses, although they do make it easier to understand:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    or
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by gak67; 07-16-2014 at 09:14 PM.

  21. #21
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count full weeks Monday thru Sunday between 2 dates

    Quote Originally Posted by gak67 View Post
    If you're after compact you don't need all the parentheses
    Ooops, looks like we do:

    11/28/2010 - 12/10/2014

    =(E2-WEEKDAY(E2)+1-D2-WEEKDAY(D2-2)+7+1)/7

    Returns 210.2857

  22. #22
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Count full weeks Monday thru Sunday between 2 dates

    Sorry - right idea, but wrong execution.

    Without the parentheses it should be
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    And reduced to
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  23. #23
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Count full weeks Monday thru Sunday between 2 dates

    still needs a bit more tho coz if dates are close to each other you get -ve results but im tired its nearly 2:30 am here
    another idea tho
    =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(E2&":"&MAX(E2,F2-6)))+6)=1)) nope not so good must sleep
    Last edited by martindwilson; 07-16-2014 at 09:36 PM.

  24. #24
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count full weeks Monday thru Sunday between 2 dates

    We've got a fly in the ointment.

    8/19/2004 - 8/20/2004

    Both formulas:

    =(E2-WEEKDAY(E2)-D2+WEEKDAY(D2-2)-5)/7

    =((E2-WEEKDAY(E2)+1)-(D2-WEEKDAY(D2-2)+7)+1)/7

    Return -1

  25. #25
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count full weeks Monday thru Sunday between 2 dates

    Maybe just add a MAX function:

    =MAX(0,(E2-WEEKDAY(E2)-D2+WEEKDAY(D2-2)-5)/7)

    Still testing...

  26. #26
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Count full weeks Monday thru Sunday between 2 dates

    The MAX is probably a good idea. It would mean it would also show a 0 if you accidentally put a start date that's later than the end date.

  27. #27
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Count full weeks Monday thru Sunday between 2 dates

    Can I ask, is this something you intend to use, or is it just a challenge you set for yourself and anybody who came across it?

    It's been cool seeing the flaws in previous approaches and building off each other's ideas to come up with an 'optimal' solution.

  28. #28
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count full weeks Monday thru Sunday between 2 dates

    There was another post today where the OP wanted to count how many weekends within a date range.

    I made a suggestion but as it turns out I misunderstood what the OP wanted. I thought they wanted to count Saturdays and Sundays within the date range.

    Then I got to thinking, OK, counting weekends should be easy. How about counting full weeks (Mon thru Sun) within a date range?

    And so I came up with that long ugly array formula (which has a bug).

    Then I decided to put up this thread and see where it would go.

    As far as intending to use this formula...

    I have no specific need at this time but it's something I want to add to my massive formula database!

    I have a database of formulas that will do everything (well, almost everything).

    I've been participating in Excel forums since the early 2000s. I've learned a lot and have answered very many questions. Whenever I answer a non-trivial unique question I add a file with that solution to my database. I currently have 1415 sample files (some are duplicates) in the database. Each file has many examples of formulas to accomplish some task. I add to this database on an almost daily basis.

  29. #29
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count full weeks Monday thru Sunday between 2 dates

    I think we're as good as we're going to get on this one.

    Thanks for the ideas and input.


  30. #30
    Forum Contributor
    Join Date
    05-04-2014
    Location
    New Jersey, United States
    MS-Off Ver
    Excel 2010, 2013
    Posts
    120

    Re: Count full weeks Monday thru Sunday between 2 dates

    I don't know if there was a solution given which didn't have some kind of issue, but give this one a try (Confirm with ctrl+shift+enter):

    Please Login or Register  to view this content.

    Where
    A1 is the start date of a range and
    B1 is the end date of a range

  31. #31
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count full weeks Monday thru Sunday between 2 dates

    Yeah, that works.

    The "final" version we arrived at is in post #25.

    Kind of hard to refine it any more than that!

    Thanks for the effort!

  32. #32
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Count full weeks Monday thru Sunday between 2 dates

    Quote Originally Posted by Tony Valko View Post
    daddylonglegs, you out there?
    Hey Tony!

    To count full weeks Monday to Sunday is really just the same as counting Sundays within a date range that begins a day earlier and then subtracting 1. As such you can use a variation on my usual INT/WEEKDAY formula.....but using TRUNC instead of INT to avoid any -1 results, i.e.

    =TRUNC((2-WEEKDAY(E2)+E2-D2)/7)

    .....or in Excel 2010 or later this version should more transparently match my description

    =MAX(0,NETWORKDAYS.INTL(D2-1,E2,"1111110")-1)
    Last edited by daddylonglegs; 07-28-2014 at 10:27 AM.
    Audere est facere

  33. #33
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count full weeks Monday thru Sunday between 2 dates

    Quote Originally Posted by Tony Valko View Post

    I'm thinking that daddylonglegs, the undisputed date formula master, has something a bit more compact.
    Quote Originally Posted by daddylonglegs View Post

    =TRUNC((2-WEEKDAY(E2)+E2-D2)/7)
    Nice one!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Monday and Sunday of the current week
    By mtt23 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-24-2012, 09:30 AM
  2. How to count full weeks
    By tillyb in forum Excel General
    Replies: 4
    Last Post: 11-06-2011, 08:12 PM
  3. adding day (sunday, Monday) to date
    By RickyDee in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  4. [SOLVED] adding day (sunday, Monday) to date
    By RickyDee in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1