+ Reply to Thread
Results 1 to 22 of 22

formula to get number of working days in a month based on criteria

  1. #1
    Forum Contributor
    Join Date
    03-28-2008
    Location
    India, bangalore
    MS-Off Ver
    Excel 2003,2007
    Posts
    216

    formula to get number of working days in a month based on criteria

    Hi,


    I need your help with the formula to get no of working days in a month by an employee based on the "In" & "Out" tagging
    In Row 1 I have the header , In , Out, In, Out, In Out, In Out ( repeated 4 times , since an employee can take leave 4 time a month and come back)

    Eg, employee Number 1, came to office on 1 March 2016 which shows under "in" & the date is showing 28th Mar 2016 unders Out which means he was in office for 28 days, & again "in" date shows 30th march which means, he came back to office on 30th match and Out date shows 15 April, which means 1 days comes in March and 14 day comes in April


    I know my question may not be that clear, you might understand when you see a sample file that is attached.

    can someone help me to get this by formula?

    thanks for your help
    Regards
    Arvind
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: formula to get number of working days in a month based on criteria

    Quote Originally Posted by aravindhan_31 View Post
    I need your help with the formula to get no of working days in a month by an employee based on the "In" & "Out" tagging
    Working days, or all days?

    Your example results suggest working every day in the in - out period, no weekend rest etc.

  3. #3
    Forum Contributor
    Join Date
    03-28-2008
    Location
    India, bangalore
    MS-Off Ver
    Excel 2003,2007
    Posts
    216

    Re: formula to get number of working days in a month based on criteria

    Sorry, including week ends. number of days

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: formula to get number of working days in a month based on criteria

    VBA solution:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    03-28-2008
    Location
    India, bangalore
    MS-Off Ver
    Excel 2003,2007
    Posts
    216

    Re: formula to get number of working days in a month based on criteria

    Hi .

    Thanks for the help, can you help me with the formula? I have about 100,000 rows.

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: formula to get number of working days in a month based on criteria

    You will not be able to do it with a formula, that is why John has provided a vba solution for you.

  7. #7
    Forum Contributor
    Join Date
    03-28-2008
    Location
    India, bangalore
    MS-Off Ver
    Excel 2003,2007
    Posts
    216

    Re: formula to get number of working days in a month based on criteria

    Hi,

    Great work! The VBA works fine

    If I have to insert 3 columns after Column A , what changes do I make?, also the macro is not giving the right result for the 12 months between Sep 2015 to Aug 2016.

    my sincere apologies for not mentioning this earlier.
    attached the sample

    Regards
    Arvind
    Attached Files Attached Files
    Last edited by aravindhan_31; 08-05-2016 at 06:08 AM.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: formula to get number of working days in a month based on criteria

    Updated code which works with "Actual Structure" sheet: change sheet name highlighted in red

    Please Login or Register  to view this content.
    please note:

    I created a named range called "Periods" which is the dates in P2:AA2.

    The dates are changed to be the FIRST (not last) day of the month which allowed me to use the MATCH function in the macro to find the start & end months.
    Attached Files Attached Files
    Last edited by JohnTopley; 08-05-2016 at 02:53 PM.

  9. #9
    Forum Contributor
    Join Date
    03-28-2008
    Location
    India, bangalore
    MS-Off Ver
    Excel 2003,2007
    Posts
    216

    Re: formula to get number of working days in a month based on criteria

    Hi,

    This is almost working, i just noticed that this is not working in the 2 below scenarios

    1. If I have only one date and all other columns are blank ( which means only one in date and no outdate at all )
    2. If the start In date is lesser than 1-sep-2015.

    Can you help me to fix this as well.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: formula to get number of working days in a month based on criteria

    Can you help me to fix this as well.
    If you tell me ...

    1. What is the result if there is no Outdate?

    2. If the start date is less than the date of the first period , what do you want to happen?
    Last edited by JohnTopley; 08-07-2016 at 08:25 AM.

  11. #11
    Forum Contributor
    Join Date
    03-28-2008
    Location
    India, bangalore
    MS-Off Ver
    Excel 2003,2007
    Posts
    216

    Re: formula to get number of working days in a month based on criteria

    If you tell me ...

    1. What is the result if there is no Outdate?
    THe result is throwing up un error and stops here
    Please Login or Register  to view this content.
    2. If the start date is less than the date of the first period , what do you want to happen?
    if the start is less than Sep 15, then assume the person is "In" before the sep 15 & hence continue the maco as is.

    Thanks for your help

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: formula to get number of working days in a month based on criteria

    You still haven't answered the question re Outdate: If there is no Outdate do we ignore the calculation?

  13. #13
    Forum Contributor
    Join Date
    03-28-2008
    Location
    India, bangalore
    MS-Off Ver
    Excel 2003,2007
    Posts
    216

    Re: formula to get number of working days in a month based on criteria

    sorry for not being clear


    If there is no outdate, then calculation has to be done for all the months from Sep 15 to August 16, which means by default full month number days will be the result

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: formula to get number of working days in a month based on criteria

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by JohnTopley; 08-08-2016 at 04:17 AM.

  15. #15
    Forum Contributor
    Join Date
    03-28-2008
    Location
    India, bangalore
    MS-Off Ver
    Excel 2003,2007
    Posts
    216

    Re: formula to get number of working days in a month based on criteria

    Hi,

    almost worked,
    1. For some strange reason for all August 2016, I am getting "1" as my result, but if there are any out date for august in my left table then its coming correctly.
    2. 1 day is showing extra for all out dates, suppose if Out date shows "15 Jan" then the number of days should be excluding 15 Jan, coz the persson was out on that day.

    i guess its a quick fix, it will be great if you help me on this

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: formula to get number of working days in a month based on criteria

    for 2. I just changed the calculating to Out-In rather than Out-IN+1.

    re 1. I don't understand this: post sample showing problem.

    I am going to re-write the macro to better cater for the new conditions. I will post as soon as I can.
    Last edited by JohnTopley; 08-08-2016 at 10:55 AM.

  17. #17
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: formula to get number of working days in a month based on criteria

    New code: please check out results

    It uses a "Work area" in AD:AM to calculate IN/OUT periods

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by JohnTopley; 08-09-2016 at 01:29 PM.

  18. #18
    Forum Contributor
    Join Date
    03-28-2008
    Location
    India, bangalore
    MS-Off Ver
    Excel 2003,2007
    Posts
    216

    Re: formula to get number of working days in a month based on criteria

    very very thanks for your help on this,

    I have attached the sample file with my observations for various scenarios ( plz refer comments)

    Thanks for your help
    Attached Files Attached Files

  19. #19
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: formula to get number of working days in a month based on criteria

    Yet again you have changed the rules: I assume not unreasonabaly that in/out dates are in chronological order not "randomly" as it appears in your sample.

    I don't expect OUT on July 16 followed by IN on Sept 15th.

    I am not willing to spend any more time on this.

  20. #20
    Forum Contributor
    Join Date
    03-28-2008
    Location
    India, bangalore
    MS-Off Ver
    Excel 2003,2007
    Posts
    216

    Re: formula to get number of working days in a month based on criteria

    Hi,

    my sincere apologies for this. I didnt mean to change the logic, this was the observation when i used my original data.

    I can somehow get the "random" things in chronological order for this file, how about the rest of observations?
    number of days for "out" is giving 1 day extra & if out date is blank before the 2nd in then consider only In date from 1 sep 2015? ( as per my example)

    Again please accept my sincere apologies & help me to close this.

  21. #21
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: formula to get number of working days in a month based on criteria

    See attached: corrections only to day counts

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by JohnTopley; 08-09-2016 at 02:45 PM.

  22. #22
    Forum Contributor
    Join Date
    03-28-2008
    Location
    India, bangalore
    MS-Off Ver
    Excel 2003,2007
    Posts
    216

    Re: formula to get number of working days in a month based on criteria

    Hi,

    Thanks it worked

+ 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. Calculate the number of working days month wise in a given period using VBA
    By rakesh3235 in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 08-21-2014, 07:00 AM
  2. vba code needed to calculate the number of working days thus far for the month
    By Jim885 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-22-2014, 09:38 PM
  3. Replies: 12
    Last Post: 02-08-2013, 02:26 PM
  4. Replies: 7
    Last Post: 01-28-2013, 12:20 PM
  5. Replies: 11
    Last Post: 06-01-2012, 08:47 AM
  6. [SOLVED] Number of working days in a month
    By Sammitry in forum Excel General
    Replies: 8
    Last Post: 05-18-2012, 08:12 AM
  7. Number of Working Days in a month
    By Talbot in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-18-2008, 05:36 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1