days in week?

Hi i have spent ages trying to work this out! and now have resorted to this forum for help.
I need a spreadsheet to auto calculate the month and no of days once a Week Commencing date has been provided. I have provide a problematic example below which shows WC 28/07/08 being part of 2 different months.

I need this so that when my colleagues come into work each monday - they can input the previous weeks activities in other columns. however, when reporting - i will be using a pivot table on the data to extract whole calender months worth of data and so need the month shown as below.

please can anyone help with the formulas i need for the month and days columns.

I HAVE INSERTED * TO SHOW SEPERATORS INCASE THE TABLE APPEARS WITHOUT SPACES:

WC (Mon - Sun) *Month *Days
28/07/08 *July *4
28/07/08 *August *3
04/08/08 *August *7
11/08/08 *August *7
18/08/08 *August *7
25/08/08 *August *7

thanks for your responce.

the example in my question is the desired outcome. the week commencing will be in column a. based on the WC date i want the formulas to calculate column B(Month) and C(no of days).

please note: in the example, 28/07/08 appears twice! once to represent a week in july (4 days) and once to represent august(3 days). column a could have a formula which i could drag down so it auto displays all WC dates beggining monday except in such cases as the example of 28/07/08.

hope you understand my question.

Please Login or Register  to view this content.

A2: Week commencing

A3 and down: =A2 + 7 * OR(A2 = A1, A2 + 7 <= EOMONTH(A2,0))

B2 and down: = TEXT(EOMONTH(A2, --(A2=A1) ), "mmmm")

C2 and down: =MIN( EOMONTH(A2, 0) - A2 + 1, 7)

EXACTLY WHAT IM AFTER!! THANK YOU!!!

Just 1 little thing:
WC 28/07/08 for july and august are both showing 4 days?? should be 4 and 3. i tried tweaking the formula, but also get problems with WC 27/10/08. Any ideas?

actually i have tweaked ur formula for C2 and down to:
=IF(A2=A1,7-C1,MIN( EOMONTH(A2, 0) - A2 + 1, 7))

that seams to overcome that little problem, but shown another. i now have a wc 24/11 for november showing 7 days which is correct - but i also have a wc 24/11 for december showing 0 days?? a wc 24/11 for dec should not exist?

thank you so much!!!

though id give you feedback.
i added what you sent me and made very small changes:

a3 and down:=A2 + 7 * OR(A2 = A1, A2 + 6 <= EOMONTH(A2,0))
b2 and down:= TEXT(EOMONTH(A2, --(A2=A1) ), "mmmm")
c2 and down:=IF(A2=A1,7-C1,MIN( EOMONTH(A2, 0) - A2 + 1, 7))

once again...thank you so much!!!

You're welcome, and thanks for the feedback. It's a pleasure to help someone that can correct my mistakes