Formula Help (DatedIF vs SumIFs vs... ?)

[Kristin]

Hello,
I am stumped. In my work I use excel spreadsheets for billing with sub-contractors that provide employment services to people with developmental disabilities. I would like to track “how long it takes an agency to find jobs for customers”. In my spreadsheets, I have a line for each customer, then aggregate information on a separate tab we call the “dashboard”.

There are few different scenarios that could happen when trying to calculate this information, and they are as follows:
1. Customer A starts with Agency, and is found a job as a later date. In this case, I could use a simple DatedIF statement like =DATEDIF(BR14,B14,"m")] which states subtract the hire date from the service start date.
2. Customer B starts with Agency, and hasn’t found a job yet. In this case, I could also use the DatedIF statement, but point it to subtracting the current billing month from the service start date.
3. Customer C starts with Agency under a different funder’s contract and joins our tracking when they come out of that system with a job. In this case, I would like our agencies to enter an “alternate job search start date” that reflects when they started serving the customer under the other contract and then I could do some formula that subtracts “job hire date” from “alternate job search start date”.
4. Customer D is served by Agency, got a job, lost a job, got a new job. This is another that would utilize the agency entering an “alternate job search start date”, which would be the most recent job termination date. So the formula would subtract “job hire date” from “alternate job search start date”.
5. Customer E is served by Agency, got a job, lost a job, and is still looking for a new job. This is another that would utilize the agency entering an “alternate job search start date”, which would be the most recent job termination date. So the formula would subtract “current billing month” from “alternate job search start date”.
6. Last example: customer transfers agencies with a job. If I were to do just a simpler service start date minus the job start date, I would end up with a negative. So I would like to utilize the IfError statement so that it puts ‘0’ for these instances.

I know this is possible. I just have been wracking my brain too long and think I’m overlooking a simpler way.
I would really like to have the answer for any of these situations returned in one column (so complex formula of IFs) vs separate columns for each scenario.
I would attach an example if I could figure out how, hopefully the information I provided above is detailed enough.

Thank you so much for your help in advance!
Kristin

 

[RobertSF]

Hell, no, you're not overlooking a simpler way. The situation you describe really is complex!

The question "how long it takes an agency to find someone a job" is simple. It's the difference between DateStart and DateEnd (or whatever you call them). The complexity lies in the fact that DateStart is defined differently for each customer type. I think the solution is to push the decision of which definition of DateStart to use down to the customer type. Is it possible to keep each customer type in its own sheet, each calculating it's own DateStart, and then have a main sheet that brings them all in and just calculates the difference?

 

[Kristin]

Thank you for the response RobertSF. In a sense this response alone is helpful to know that I'm not just being dense. lol

I keep thinking I should be able to use something like this: SUMIF(AJ6,"P",(DatedIf(BR6,BS6,M))), but it's just not working.
I can't really separate out the customers by the different stages of service they are in, BUT that made me think... I could create a field that the agencies managed a code that reflected what situation the customer fell within. Then base the formula on that??
Does that get any juices flowing for additional ideas?

Thanks again for your help.
Kristin

 

[thisoldman]

This is what I drew from your first post. Is it correct?

Customer A, simple case:
    Employed
    One start date
    One hire date
    No termination date
    Start_Date = Maximum_Start_Date
    End_Date = Maximum_Hire_Date

Customer B, simple case:
    Not employed
    One start date
    No hire date
    No termination date
    Start_Date = Maximum_Start_Date
    End_Date = Current_Date

Customer C, other contract:
    Employed
    Two start dates
    Two hire dates
    One termination date
    Start_Date = Minimum_Start_Date
    End_Date = Maximum_Hire_Date

Customer D, on 2nd job:
    Employed
    Two start dates
    Two hire dates
    One termination date
    Start_Date = Termination1_Date
    End_Date = Maximum_Start_Date

Customer E, on 2nd search:
    Not employed
    One start date
    One hire date
    One termination date
    Start_Date = Termination1_Date
    End_Date = Current_Date

Customer F, job transfer:
    Employed
    Start_Date = End_Date

 

[Kristin]

This is what I drew from your first post. Is it correct?

Hi thisoldman, yes that is correct.

How would I use that Coding though? I'm only familiar with using formulas.

Thanks,
Kristin

 

[thisoldman]

I did not mean to suggest a VBA solution, I was checking that what I gleaned from your explanation was correct. Sorry for the misunderstanding.

I assume that if there's no Hire date or no Termination date then those cells are left blank or have an empty string. MAX and MIN ignore empty cells and non-booleans and non-numeric text—the MAX or MIN of empty cells returns zero.

This is what I came up with:

=IF(Job_Transfer,
  0,
  DATEDIF(IF(OR(NOT(Termination_Date), Other_Contract)
             MIN(Start_Dates),
             Termination_Date),
          IF(COUNT(Hire_Dates) = COUNT(Termination_Dates),
             Current_Date,
             IF(OR(Termination_Date, Other_Contract),
                MAX(Hire_Dates),
                MAX(Start_Dates))),
          "m"))

The DATEDIF start is in blue, the end is in red.

If you prefer a formula without line breaks:

=IF(Job_Transfer, 0, DATEDIF(IF(OR(NOT(Termination_Date), Other_Contract) MIN(Start_Dates), Termination_Date), IF(COUNT(Hire_Dates) = COUNT(Termination_Dates), Current_Date, IF(OR(Termination_Date, Other_Contract), MAX(Hire_Dates), MAX(Start_Dates))), "m"))