+ Reply to Thread
Results 1 to 7 of 7

Nesting Issue

  1. #1
    Registered User
    Join Date
    05-26-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    1

    Nesting Issue

    I need help with my formula. My end goal to have a formula that will tell me how many days someone has been working on a particular project regardless of start or stop dates. What I have entered in the attached spreadsheet is

    =IF(AND(C2<="10/1/2012"+0,D2>="10/31/2012"+0),22,"x")

    but it's only helpful if the person was there the entire month. If the person was not there the entire month I get the "X" and then have to go in a manually figure out how many days they worked. Doing this every pay period for a large number of people is proving to be too time consuming. I was hoping to create a formula that was something like this

    =IF(AND(C2<="10/1/2012"+0,D2>="10/31/2012"+0),23,C2<="10/1/2012"+0,D2>="10/30/2012"+0),22,C2<="10/1/2012"+0,D2>="10/29/2012"+0),21,C2<="10/1/2012"+0,D2>="10/28/2012"+0),20,
    C2<="10/1/2012"+0,D2>="10/27/2012"+0),20,C2<="10/1/2012"+0,D2>="10/26/2012"+0),20,C2<="10/1/2012"+0,D2>="10/25/2012"+0),19,C2<="10/1/2012"+0,D2>="10/24/2012"+0),18, ................. C2<="10/1/2012"+0,D2>="10/2/2012"+0),3,C2<="10/1/2012"+0,D2>="10/1/2012"+0),1,OR(C2>"10/31/2012"+0,0,D2<"10/1/2012"+0),0,0))

    This way if they were there for any time at all that month I would get the correct number of days. If they were there from the before beginning of October and left October 5 it will return "5". If they started on October 15 and ended October 19 it will return a "5" if they started October 30 and stayed past the end of the month it will return a "2" and so on. Also, if they did not work any days that month it will return a "0" instead of the "X".


    The attachment is a simplified example of the spreadsheet I'm using to calculate the number of days worked and project the number of days expected to be worked on a project by person and by office. I'm being asked to provide these numbers by month and by grand total. Most of the "X"s are zeros but every pay period when I get new data and I add new people or change dates I have to go through and recalculate everything manually.

    How can I make this task easier?
    Is this even the right approach?
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    05-14-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    187

    Re: Nesting Issue

    Try using Count Function to find the number of dates which do not have X that shoud be your answer if you were doing for certain time period
    If you want to do monthwise then may be PIvot Table might be more useful

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,082

    Re: Nesting Issue

    this this formula, copied across and down the entire table...

    =IF(IF($D2<=G$1-1,$D2,G$1-1)-IF($C2>F$1,$C2,F$1)<=0,"x",IF($D2<=G$1-1,$D2,G$1-1)-IF($C2>F$1,$C2,F$1))

    let me know if this gives you what you need?
    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

  4. #4
    Forum Contributor
    Join Date
    05-14-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    187

    Re: Nesting Issue

    Hi Mike,

    Is it possible to upload a sample data from which you extract the month wise report you posted earlier? That way it would be easier for us suggest something better.

    Regards,

    Veejar

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,082

    Re: Nesting Issue

    he attached a file in his 1st post

  6. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Nesting Issue

    I've got a feeling you're going for working days (based on the mysterious 22 used in your formula being the # of working days in January (w/o taking off holiday)) so my offer would be:

    In F2: =IF(MIN($D2,G$1-1)-MAX($C2,F$1)>0,NETWORKDAYS(MAX($C2,F$1),MIN($D2,G$1-1)),"x")
    this is dragged down and over to and including column P
    This would go in Q2 and dragged down: =IF(MIN($D2,Q$1+30)-MAX($C2,Q$1)>0,NETWORKDAYS(MAX($C2,Q$1),MIN($D2,Q$1+30)),"x")

    not tested for accuracy - that's your job - and does not account for holidays but can easily do so by having a list of holidays somewhere and amend the NETWORKDAYS() function to include that reference.
    Here is a link to explain that function: http://www.excelfunctions.net/Excel-...-Function.html

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,984

    Re: Nesting Issue

    Pl see the attached file.

    Formulas are available in Sheet1(2).
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 05-28-2012 at 05:30 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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