# Count full weeks Monday thru Sunday between 2 dates

1. ## 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?

2. ## Re: Count full weeks Monday thru Sunday between 2 dates

Shouldn't this work?

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

3. ## Re: Count full weeks Monday thru Sunday between 2 dates

Originally Posted by hoyasaxa215
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.

4. ## Re: Count full weeks Monday thru Sunday between 2 dates

Originally Posted by hoyasaxa215
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. ## 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

6. ## Re: Count full weeks Monday thru Sunday between 2 dates

Originally Posted by AlKey
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. ## Re: Count full weeks Monday thru Sunday between 2 dates

Maybe this?
Formula:
`Please Login or Register  to view this content.`

8. ## 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. ## Re: Count full weeks Monday thru Sunday between 2 dates

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

10. ## Re: Count full weeks Monday thru Sunday between 2 dates

Originally Posted by gak67
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. ## Re: Count full weeks Monday thru Sunday between 2 dates

Originally Posted by gak67
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. ## 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. ## 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. ## Re: Count full weeks Monday thru Sunday between 2 dates

Originally Posted by AlKey
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. ## Re: Count full weeks Monday thru Sunday between 2 dates

Originally Posted by gak67
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. ## 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

17. ## Re: Count full weeks Monday thru Sunday between 2 dates

Originally Posted by Tony Valko
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. ## Re: Count full weeks Monday thru Sunday between 2 dates

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

19. ## Re: Count full weeks Monday thru Sunday between 2 dates

Originally Posted by martindwilson
=((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. ## 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:
`Please Login or Register  to view this content.`
or
Formula:
`Please Login or Register  to view this content.`

21. ## Re: Count full weeks Monday thru Sunday between 2 dates

Originally Posted by gak67
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. ## Re: Count full weeks Monday thru Sunday between 2 dates

Sorry - right idea, but wrong execution.

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

23. ## 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

24. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Count full weeks Monday thru Sunday between 2 dates

Originally Posted by Tony Valko
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)

33. ## Re: Count full weeks Monday thru Sunday between 2 dates

Originally Posted by Tony Valko

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

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

##### Users Browsing this Thread

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

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