+ Reply to Thread
Results 1 to 3 of 3

Separating days by Year/Week in a date range :)

  1. #1
    Registered User
    Join Date
    08-17-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Cool Separating days by Year/Week in a date range :)

    With the date ranges I am calculating what Week they start in using =TRUNC(((C2-DATE(YEAR(C2),1,0))+6)/7) which results in 1(Week 1). I am using this result to then calculate the % utilization based on 40 hours per week. It is fine if the date range is in the same week, 08/16/2012 08/20/2012, but when the date range spans weeks like 09/01/2012 09/23/2012 how can I modify/rewrite the formula to show the work days in each week covered by the range?

    09/01/2012 to 09/23/2012

    09/01/2012 is in Week 35 and is a Saturday so no work days
    09/02/2012 is in Week 36(I guess the default week is Sunday to Sunday?) and is a Sunday so no work days
    09/03/2012 to 09/07/2012 is Week 36 with a total of 5 work days
    09/10/2012 to 09/14/2012 is Week 37 with a total of 5 work days
    09/17/2012 to 09/21/2012 is Week 38 with a total of 5 work days
    09/22/2012 is in week 38 and is a Saturday so no work days
    09/23/2012 is in week 39 and is a Sunday so no work days

    So I show week 36 with 15 work days instead of 36 with 5 work days, week 37 with 5 work days and week 38 with 5 work days.

    This is a copy of the sheet with some sample data in it
    Attachment 174894

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Separating days by Year/Week in a date range :)

    have you tried to just use weeknum() and weekday() ?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    08-17-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Separating days by Year/Week in a date range :)

    Quote Originally Posted by FDibbins View Post
    have you tried to just use weeknum() and weekday() ?
    I am having issues combining it with =workdays, not sure how to wrap it so I return the week number and the number of workdays in that week!!!!

    Thanks for taking the time to look at the issue

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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