Count Number Of Days Except Sunday

[mgirvin]

Dear Smartest Excel People In The World,

In B13 I have the start date: 12/1/2008
In C13 I have the end date: 1/5/2009
In cell D13 I want a formula that counts the number of days between the two dates that are not Sunday. The start and end dates are included in the count.

I have created this formula using Ctrl + Shift + Enter:

{=SUM(IF(WEEKDAY(ROW(INDIRECT(B13&":"&C13)),2)<>7,1,0))}

I have also created this formula using Ctrl + Shift + Enter:

{=SUM(IF(TEXT(ROW(INDIRECT(B13&":"&C13)),"ddd")<>"Sun",1,0))}

They both seem to work. I get a result of 31.

Is there a formula that is better than this, more efficient than this, or "less expensive"?

 

[mgirvin]

Dear houdini,

Now that is an efficient and cleaver formula! Can you explain the logic of it to me?

Thanks!

 

[mgirvin]

Do you plan, Mike, to evaluate which formula is fastest? It would be interesting to know.

Dear Fazza,

Sure, but the unit of time I know is "Enter" and "Ctrl + Shift = Enter". So they each tie. No, no! I don't know how to measure which is the fastest. Do you know how?

 

[barry houdini]

Hello Mike,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
To explain it’s probably better to do so using a slightly different formula. This formula will calculate the number of Sundays between 2 dates, A2 and B2<o></o>
<o> </o>
=INT((WEEKDAY(A2-1)+B2-A2)/7)<o></o>
<o> </o>
The explanation is fairly intuitive<o></o>
<o></o>
If you have any time period 6 days long, e.g. Monday to Sunday (including 7 different days) then how many Sundays are there in that period (assuming we count both start and end dates)? The answer is 1. Similarly any 13 day period has 2 Sundays....etc.<o></o>
<o></o>
.....but what if the period is 7 days long, how many Sundays will there be? The answer is 1.....unless the start date is a Sunday, in which case the answer is 2...or what about 8 days......again the answer is 1....unless the start date is a Saturday or Sunday.<o></o>
<o></o>
So it's sufficient to know 2 things, the length of the period and the weekday of the start date.<o></o>
<o></o>
Given the above logic if we calculate the length of the period and add 7 if the start date is a Sunday, add 6 if the start date is a Saturday, add 5 if the start date is a Friday etc….then divide the result by 7 and round down to the nearest integer, we’ll get the number of Sundays<o></o>
<o> </o>
So B2-A2 gives the length of the period and WEEKDAY(A2-1) adds 7 for Sunday, 6 for Saturday etc. then you divide the whole amount by 7 and INT rounds down.<o></o>
<o> </o>
You can adapt that formula to count any day of the week, change the 1 to a 2 to count Mondays, 3 for Tuesdays, 4 for Wednesdays etc.<o></o>
<o> </o>
Of course you wanted to count days that aren’t Sundays so to do that you can subtract the above from the total number of days, i.e.<o></o>
<o> </o>
=B2-A2+1-INT((WEEKDAY(A2-1)+B2-A2)/7)<o></o>
<o> </o>
The formula I posted originally uses the same concepts but examines the weekday of the end date, so you end up with a slightly different formula, i.e.<o></o>
<o> </o>
=B2-A2-INT((1+B2-A2-WEEKDAY(B2))/7)<o></o>
<o> </o>
Both of these will give exactly the same result. The second one derives from a slightly different way to count Sundays between 2 dates, i.e.<o></o>
<o> </o>
=INT((8-WEEKDAY(B2)+B2-A2)/7)<o></o>
<o> </o>
This formula only references the start date once, so it’s useful to use when the start date has to be derived from a calculation, you only have to include that calculation once.<o></o>
<o> </o>
Note: one more way to count the non–Sundays, using a similar approach<o></o>
<o> </o>
=SUM(INT((WEEKDAY(A2-{2,3,4,5,6,7})+B2-A2)/7))<o></o>
<o> </o>
In this formula the “array constant” {2,3,4,5,6,7} represents <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:City w:st="on"><st1lace w:st="on">Mons</st1lace></st1:City>, Tues, Weds, Thurs, Fris, Sats. This version is useful because you can adapt it to count any combination of days.<o></o>
<o> </o>
You want to count Mondays and Fridays in a period?<o></o>
<o> </o>
=SUM(INT((WEEKDAY(A2-{2,6})+B2-A2)/7))<o></o>
<o> </o>
Regards, barry<o></o>

 

[Fazza]

Thanks, Barry. My formula was wrong & your formula is much smarter. Regards, Fazza

Mike, I guess for timing the formula's speed you could load up a worksheet with 60,000 formulas and have some VBA do something like

set calculation mode to manual
start timer
run a loop say 100 times
force a worksheet calculation
end loop
stop timer

For the timer, I think there is an API approach that is best, not just simply using something like dtMyTimer = Now. A quick google should fine that.

cheers, Fazza

 

[mgirvin]

Dear houdini,

Wow!

Your description is so well done! It is thorough and easy to follow.

And plus the extra knowledge for other related situations.

Thanks a million!

 

[mgirvin]

Dear Fazza,

Thanks! I will try a quick google!

 

[mgirvin]

Dear houdini,

Opps!

I thought I got it, but I did not understand this part:

"If you have any time period 6 days long, e.g. Monday to Sunday (including 7 different days)"

When I count Monday to Sunday, I count 7.

What am I missing?

 

[barry houdini]

Well, it's 6 days long if you calculate enddate-startdate, e.g. yesterday was Monday 5th, Sunday is the 11th so

=11-5 = 6

.....but obviously if you count the days, including start date and end date, there are 7

It's an old argument really......but doesn't make much difference to the other calculations, they're still valid either way

 

[shemayisroel]

Well, it's 6 days long if you calculate enddate-startdate, e.g. yesterday was Monday 5th, Sunday is the 11th so

=11-5 = 6

.....but obviously if you count the days, including start date and end date, there are 7

It's an old argument really......but doesn't make much difference to the other calculations, they're still valid either way

True, old argument that I usually see you in providing solutions for allot of the times on this forum

 

[mgirvin]

Dear houdini,

Whew! I am glad that it is that simple.

Thanks.