Sunday, June 23, 2013

yogi_ Determine Availability Of Staff By Name From Data In Project Management Info Table

                                          Google Spreadsheet   Post  #1259
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Jun 23, 2013
user CK Vijay Bhaskar (!category-topic/docs/spreadsheets/DbKsYuAhek0)
Multiple Condition Issue
This is my problem statement - 
To check if a person is free between a given set of date range. 
I tried searching in the group, and I incorporated some of the suggestions based which I got the solution for Excel, but not for Google Spreadsheet. 
I have a task sheet with multiple set of tasks assigned to various people. Each row as the task name/details, person responsible, plan/actual start and end date.
I am trying to determine the availability of the person based on the plan start/end date across the tasks.
I was successfully able to do this in Excel using this formula - 
Where -
Data!D:D has the range containing all the people
$C$1 - is the name against which I am searching
A2 = is the date which I am checking if it is falling between two date ranges. The A column contains the days within each month against which I am checking if the person is available or not.
Data!I:I = contains the range of plan start date
Data!J:J = contains the range of plan end date
But when I tried to do this in Google Spreadsheet, It is not giving me the the expected result. The formula I have used is this -
=IF(SUMPRODUCT(('Data'!$G:$G=$B$4)*(C$2>='Data'!$L:$L)*(C$2<='Data'!$M:$M))>0,"No","Yes"), where - Data!$G$G = name range
$B$4 = is the name against which I am searching
C$2 = date row
Data!$L:$L = Plan start Date range
Data!$M:$M = Plan end Date range
Any help would be great. Thanks in advance for your time. 
I was taking time to remove unwanted data.

As you will see, I need to track against the date if a person has planned a task or not so that I can see if the person is available or not for a particular date.

Hope this helps. 
Please do let me know if you would need some more info to arrive at the correct solution.