+ Reply to Thread
Results 1 to 8 of 8

days in week?

  1. #1
    Registered User
    Join Date
    07-31-2007
    Location
    Newcastle Upon Tyne, UK
    MS-Off Ver
    2003
    Posts
    20

    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. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Summarizing data from months

    Can you clarify your example a bit by
    also posting the results you want calculated
    from the sample data?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    07-31-2007
    Location
    Newcastle Upon Tyne, UK
    MS-Off Ver
    2003
    Posts
    20
    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. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    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. #5
    Registered User
    Join Date
    07-31-2007
    Location
    Newcastle Upon Tyne, UK
    MS-Off Ver
    2003
    Posts
    20
    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. #6
    Registered User
    Join Date
    07-31-2007
    Location
    Newcastle Upon Tyne, UK
    MS-Off Ver
    2003
    Posts
    20
    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. #7
    Registered User
    Join Date
    07-31-2007
    Location
    Newcastle Upon Tyne, UK
    MS-Off Ver
    2003
    Posts
    20
    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. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    You're welcome, and thanks for the feedback. It's a pleasure to help someone that can correct my mistakes

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Determine future dates based on selected days of the week
    By hnowack in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-26-2008, 09:51 PM
  2. Using days of the week to compute work hours
    By ronhc213 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2008, 06:46 PM
  3. Replies: 16
    Last Post: 03-27-2007, 02:14 AM
  4. Statistical Anomalies
    By Gurblash in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-21-2007, 04:56 PM
  5. Counting days worked and Averaging Totals
    By lilbpaw in forum Excel General
    Replies: 16
    Last Post: 11-21-2006, 04:38 PM

Bookmarks

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