days of the week per month

dave3009

Well-known Member

Joined

Jun 23, 2006

Messages

7,146

Office Version

1. 365
2. 2021

Platform

1. Windows
2. Mobile
3. Web

• Jun 25, 2009

• #2

Hi Steve

Welcome to the Board

=SUMPRODUCT(--(WEEKDAY($A$1:$A$30)=1))

for a sunday, change the 1 to a 2 for a monday or reference a cell.

HTH


Dave

 

Upvote 0

T

taigovinda

Well-known Member

Joined

Mar 28, 2007

Messages

2,639

• Jun 25, 2009

• #3

Put your first and last dates in cell A1 and A2. Put a numerical value for day of the week in cell A3 (Sunday = 1, Saturday = 7).

Then use this formula. When you enter it, press control-shift-enter, not just enter.

=SUM(IF(WEEKDAY(ROW(INDIRECT("$A"&A1&":$A"&A2)))=A3,1,FALSE))

When you update your first and last dates and day of the week, the formula gives you the result. Or in the formula, copy it seven times and change A3 to 1, 2, 3, 4... Then you have a cell for each day of the week.

Tai

p.s. I love this guy's site; this specific link explains all about array formulas: http://www.cpearson.com/excel/ArrayFormulas.aspx

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Jun 25, 2009

• #4

If you have start date in A1 and end date in B1 this formula will give you a count of Mondays between those two dates (inclusive)

=INT((WEEKDAY(A1-2)+B1-A1)/7)

change the 2 to get other days (1=Sun through to 7 = Sat)

 

Upvote 0

T

taigovinda

Well-known Member

Joined

Mar 28, 2007

Messages

2,639

• Jun 25, 2009

• #5

Nice, Barry! So short and elegant. But I spent ten minutes staring at it and can't quite understand how yours works...

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Jun 25, 2009

• #6

I tried to explain the formula in this post here

 

Upvote 0

F

Flashart

New Member

Joined

Jun 25, 2009

Messages

2

• Jun 26, 2009

• #7

Hi

Thank you all for your contributions, I've just tried Barry's solution and it does the job perfectly.

Thanks again Guys.

Steve

 

Upvote 0

T

taigovinda

Well-known Member

Joined

Mar 28, 2007

Messages

2,639

• Jun 26, 2009

• #8

Beautiful! Thanks, Barry.

 

Upvote 0