+ Reply to Thread
Results 1 to 9 of 9

Count workdays for each month between two dates

  1. #1
    Registered User
    Join Date
    08-12-2013
    Location
    gr, MI
    MS-Off Ver
    Excel 2010
    Posts
    17

    Count workdays for each month between two dates

    Hello,
    Im new to this Forum and i apologize if this has already been covered (yes, ive searched google and this forum for the answer).

    here is my question, Im looking to determine the number of working days in each month that fall between the two dates


    I started using the IF function and got as far as calculating the date for one month but i get hung up when the start and stop dates range over 2 or 3 months.


    Any advice would be appreciated


    Thank you
    WorkingDays Calc.xlsx

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Count workdays for each month between two dates

    See the green cells for the solution.

    I used netto.workdays (maybe it's called differant in english) for it.

    See the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    08-12-2013
    Location
    gr, MI
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Count workdays for each month between two dates

    Ive got the total working days between the two dates but now im looking to break it into month by month. Youll see in my example that the start and stop dates are broken up by the months they include. I manually counted the number of working days the dates fell into for each month.


    7/1/2013 to 8/8/2013 for example has 29 working days in it but broken up July has 23 working days in it and August has 6 in it. Im looking for the days in each month so that i can enter a start and stop date without having to manually calculate the number of day.


    i hope this explains it a little better

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Count workdays for each month between two dates

    Try this formula in C2 copied across and down

    =MAX(0,NETWORKDAYS(MAX($A2,C$1),MIN($B2,EOMONTH(C$1,0))))

    If you don't want to see zeros then custom format cells as

    0;;
    Audere est facere

  5. #5
    Registered User
    Join Date
    08-12-2013
    Location
    gr, MI
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Count workdays for each month between two dates

    That seems to fix it for the example sheet i made but when i tried to put it into my main template it seems to add the number of days across for some reason. Also you'll see in my example that in columns R and over i am checking to see if that day is included in the range and if true it pulls the value from L. It worked before it was in a table but now that it is i am having problems. BTW, i put it in a table because i wanted to have another formulated row be added once the one above it is completed

    Packing Temp.xlsm

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Count workdays for each month between two dates

    Your data in row 1 is text and not recognised as date values - if you put valid dates in C1:J1 (1st of each month) and then in R1 across the formulas should work as required

  7. #7
    Registered User
    Join Date
    08-12-2013
    Location
    gr, MI
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Count workdays for each month between two dates

    Ive been trying different date formats but they all end up giving me the same issue. I even tried copying my format from the first example that worked over to it but still nothing.

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Count workdays for each month between two dates

    I think it's a problem to have dates as table column headers - I think Excel wants the values there to be text.....but you can still make it work - I entered 5/1/2013 in C1, 6/1/2013 in D1 etc. and although those are still text values it will make the formulas work.....

  9. #9
    Registered User
    Join Date
    08-12-2013
    Location
    gr, MI
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Count workdays for each month between two dates

    I turned off the header row under my table setting and it fixed it. Not sure why this was screwing it up but it works now.

    Thanks for the help

+ 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. How to calculate workdays in a month?
    By ExcelNewby in forum Excel General
    Replies: 4
    Last Post: 06-19-2008, 06:26 PM
  2. Formula to count workdays between two dates?
    By sportsguy in forum Excel General
    Replies: 4
    Last Post: 10-04-2007, 05:28 PM
  3. Count workdays remaining in a month
    By Ads in forum Excel General
    Replies: 2
    Last Post: 08-13-2006, 07:40 PM
  4. Count the dates in a month
    By s2m via OfficeKB.com in forum Excel General
    Replies: 5
    Last Post: 08-04-2006, 12:40 PM
  5. Dislpay count of workdays in a month?
    By DB Explorer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-21-2006, 12:20 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