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

#### Kristin

##### New Member
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.

Kristin

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### RobertSF

##### Board Regular
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

##### New Member
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?

Kristin

#### thisoldman

##### Well-known Member
This is what I drew from your first post. Is it correct?

Code:
``````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

##### New Member
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

##### Well-known Member
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:

Rich (BB code):
``````=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"))

Replies
11
Views
169
Replies
18
Views
460
Replies
3
Views
273
Replies
10
Views
537
Replies
3
Views
221

1,170,942
Messages
5,872,859
Members
432,950
Latest member
ALeXceLBr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back