# Nesting Issue

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?

2. ## 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. ## 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?

4. ## 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. ## Re: Nesting Issue

he attached a file in his 1st post

6. ## 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. ## Re: Nesting Issue

Pl see the attached file.

Formulas are available in Sheet1(2).

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