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?
Bookmarks