days in week?

1. 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

2. Summarizing data from months

Can you clarify your example a bit by
also posting the results you want calculated
from the sample data?

3. 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.

4. 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)

5. 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?

6. 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!!!

7. 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!!!

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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1