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?

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.

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.

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

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.

Maybe this?

Formula:

Please Login or Register  to view this content.

@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

Or just slightly shorter:

Formula:

Please Login or Register  to view this content.

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.

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.

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

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.

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.

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?

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

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?

Originally Posted by gak67

Is it like a loyalty scheme where you can redeem the points for prizes?

That's an excellent idea.

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.

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.

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

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.

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

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

Maybe just add a MAX function:

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

Still testing...

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.

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.

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.

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

Thanks for the ideas and input.

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!

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)

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!