+ Reply to Thread
Results 1 to 6 of 6

Calculating Workdays for each quarter from a start and end date

  1. 06-08-2015, 09:23 PM #1
    ebe
    ebe is offline
    Registered User
    Join Date
    07-22-2010
    Location
    toronto
    MS-Off Ver
    Excel 2007
    Posts
    25

    Calculating Workdays for each quarter from a start and end date

    Hi

    I am trying to calculate workdays for each quarter in the attached file based on Start dates and End dates of projects on columns A and B.. How could I do this in Excel? Any help is most appreciated?


    Thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-09-2015, 01:33 AM #2
    cbatrody
    cbatrody is offline
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Calculating Workdays for each quarter from a start and end date

    Hi,

    Please go through the following thread that had a similar requirement. I had created an IF formula to calculate the number of days in a quarter when a start date and end dates are given (excel file attached)

    http://www.excelforum.com/excel-form...are-given.html
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 06-09-2015, 02:25 AM #3
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2502
    Posts
    13,700

    Re: Calculating Workdays for each quarter from a start and end date

    The attached calculates workdays assuming Sat/Sun weekends. No holidays were considered.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Does this do what you want?

    Row\Col
    A
    B
    C
    D
    E
    F
    3
    Start
    End
    Q1 days
    Q2 Days
    Q3 Days
    Q4 Days
    4
    1-May-08
    28-Oct-09
    64
    108
    132
    86
    5
    1-Jul-08
    28-Dec-09
    64
    65
    132
    129
    6
    6-Aug-08
    2-Feb-09
    23
    0
    40
    66
    7
    5-Oct-08
    2-Apr-09
    64
    2
    0
    63
    8
    15-Oct-08
    13-Apr-10
    128
    74
    66
    122
    9
    12-Nov-08
    11-May-10
    128
    94
    66
    102
    10
    15-Dec-08
    12-Jun-09
    64
    53
    0
    13
    11
    1-Jan-09
    29-Jun-09
    64
    64
    0
    0
    12
    5-Jan-09
    30-Mar-10
    125
    65
    66
    66
    13
    15-Jan-09
    1-Dec-09
    54
    65
    66
    44
    14
    5-Feb-09
    3-Aug-09
    39
    65
    24
    0
    15
    29-Mar-09
    25-Sep-09
    2
    65
    63
    0
    16
    5-May-09
    1-Nov-09
    0
    41
    66
    22
    17
    1-Jun-09
    28-Nov-09
    0
    22
    66
    42
    18
    1-Jul-09
    28-Dec-09
    0
    0
    66
    63
    19
    1-Oct-09
    30-Mar-10
    63
    0
    0
    66
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 06-09-2015, 07:59 AM #4
    ebe
    ebe is offline
    Registered User
    Join Date
    07-22-2010
    Location
    toronto
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: Calculating Workdays for each quarter from a start and end date

    thanks for the help but looks like the formula is not working properly..like C8 or C9..there aren't 128 workdays in Q1 2009
    Reply With QuoteRegister To Reply
  5. 06-09-2015, 10:37 AM #5
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,677

    Re: Calculating Workdays for each quarter from a start and end date

    I think that FlameRetired has suggested a method that will total all the Q1 workdays, possibly from multiple years, so 128 represents all Q1 workdays in a period stretching across two years.

    You didn't make it clear what results you wanted in your attachment, but if you want the working days in each individual quarter then try this approach:

    List the start dates of each quarter in C2 across and the end date for each in the cell below, i.e. in C3 across, then with start date in A4 and end date in B4 you can use this formula in C4 copied across and down as required:

    =MAX(0,NETWORKDAYS(MAX(C$2,$A4),MIN(C$3,$B4)))
    Audere est facere
    Reply With QuoteRegister To Reply
  6. 06-09-2015, 01:00 PM #6
    ebe
    ebe is offline
    Registered User
    Join Date
    07-22-2010
    Location
    toronto
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: Calculating Workdays for each quarter from a start and end date

    awesome..this totally worked..thanks for the help
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Workdays Including Start Date and Saturdays
    By amoto in forum Excel General
    Replies: 2
    Last Post: 05-09-2013, 02:10 PM
  2. Schedule - Start Date and End Date with Unequal Workdays
    By High_Road in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-10-2013, 09:48 AM
  3. count number of workdays from a start date
    By asmith in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-05-2012, 01:12 PM
  4. Calendar days from start date and workdays
    By mcinnes01 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-17-2010, 10:04 AM
  5. Excel 2007 : Calculating Quarter Start Dates
    By mlh4041 in forum Excel General
    Replies: 2
    Last Post: 10-19-2009, 10:26 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1