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"?

[shemayisroel]

Heres another way

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B13&":"&C13)),2)<7))

[shemayisroel]

Or you could use the below but you will need to ensure you tick the add in 'Analysis Toolpack' through the tools menu option.

=NETWORKDAYS(B13,C13)+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B13&":"&C13)))=7))

[Fazza]

Without an array formula, I don't know about the speed ...

=C13-B13+(CEILING(C13,7)>CEILING(B13,7))-(CEILING(C13,7)-CEILING(B13,7))/7

[mgirvin]

Dear shemayisroel,

Thank you very much for your amazing formulas!

I like the formula:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B13&":"&C13)),2)<7))

What are the advantages to your formula over this one:

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


Also, If I use this formula:

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(B13&":"&C13)),2)<7)*1)

Instead of this one:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B13&":"&C13)),2)<7))

Is there an advantage to the double negative over the multiply by 1? I have actually been wondering about this for a while. I tend to use Multiply by 1 most of the time, but often I see the double negative at this Message Board.

Thanks!

[mgirvin]

Dear Fazza,

Wow! That is an incredible non-array formula:

=C13-B13+(CEILING(C13,7)>CEILING(B13,7))-(CEILING(C13,7)-CEILING(B13,7))/7

I have run through formula evaluator a few times and am starting to get it (not really). Can you explain the logic of how it gets the right number?

Thanks!

[shemayisroel]

Originally Posted by mgirvin

Dear shemayisroel,

Thank you very much for your amazing formulas!

I like the formula:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B13&":"&C13)),2)<7))

What are the advantages to your formula over this one:

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


Also, If I use this formula:

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(B13&":"&C13)),2)<7)*1)

Instead of this one:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B13&":"&C13)),2)<7))

Is there an advantage to the double negative over the multiply by 1? I have actually been wondering about this for a while. I tend to use Multiply by 1 most of the time, but often I see the double negative at this Message Board.

Thanks!

mgirvin your welcome,

Not sure about the advantages perhaps one of the MVP's can help as all I know is it works and yields the required result

Regarding your SUMPRODUCT query, check out the below link as there is some discussion around what you are looking for...

Hope this helps

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

[Fazza]

Originally Posted by mgirvin

=C13-B13+(CEILING(C13,7)>CEILING(B13,7))-(CEILING(C13,7)-CEILING(B13,7))/7

...

Can you explain the logic of how it gets the right number?

Sure, Mike.

C13-B13 is just the days from start to end - so it includes Sundays.

Ceiling(C13,7) is one Sunday, and Ceiling(B13,7) another.
so for Dec 1st '08 and Jan 5th '09 these are Dec 6th '08 and Jan 10th '09

the (ceiling(c13,7)>ceiling(b13,7)) adds one if the dates are in different weeks. If both b13 & c13 are the same, then this would not add 1.

and the last term takes the difference in those Sunday dates and divides by 7. so the number of Sundays is subtracted.

Maybe easiest seen by loading a few dates and seeing the results.

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

Cheers, Fazza

[barry houdini]

Originally Posted by Fazza

Without an array formula, I don't know about the speed ...

=C13-B13+(CEILING(C13,7)>CEILING(B13,7))-(CEILING(C13,7)-CEILING(B13,7))/7

I don't think it gives the right results in all circumstances. If B13 is today (Tuesday 6th January 2009) and C13 is tomorrow I get a result of 1, shouldn't that be 2?

Try

=C13-B13-INT((1+C13-B13-WEEKDAY(C13))/7)

[mgirvin]

Dear Fazza,

Thanks for the explanation! It really helps.