1 Attachment(s)
Complex If Statement needed to Calculate Date Range from Create Account to Stop Accou
I am hoping to get some help on creating a formula that basically has a big IF statement. I hope this makes sense, so here goes. I am looking to find the "age" or "elapsed days" of an ID from when they created an account to when they stopped. This might be very complex and if there's not a way to calculate the Elapsed days (even though that's what I prefer), perhaps there is some formula that can return a value like a "1" or "True" or something that lets me know the statement is true.
A kind person on this forum helped me with the original formula when there were only a couple of variables, but he stated that the below new criteria is a little too complex. His original formula is an array and it worked beautifully. However, I need to add a little more variables to the equation.
I thank you in advance for your help in recommending any solution for this.
original formula
{=IF(D2>0,A2-INDEX(A$2:D$16,MATCH(B2&"1",B$2:B$16&C$2:C$16,0),1 ),"")}
New formulas needed or revised from the above:
Formula 1: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Create to Suspend but NO Stopped)
Formula 2: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped) OR Column 6 (Unsuspend), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Suspend Only, No stopped or Unsuspend)"
Formula 3: The ID in column 2 does not have to be the same, but if there is a value in column 3 only (Create) and there is no value in the Suspend, Stopped, or Unsuspend columns, then place a "1" in the cell. My manual calc is the "Create Only" column.
Attachment 380
| Event Date |
ID |
CREATE |
Suspend |
STOPPED |
Unsuspend |
Elapsed Days (Create to Suspend but NO Stopped) |
Elapsed Days (Suspend Only, No stopped or Unsuspend) |
Create Only |
| 2/10/11 |
207135 |
1 |
|
|
|
|
|
|
| 3/8/11 |
207135 |
|
1 |
|
|
|
|
|
| 3/10/11 |
207135 |
|
|
1 |
|
|
|
|
| 2/10/11 |
207316 |
1 |
|
1 |
|
|
|
|
| 2/13/11 |
207785 |
1 |
|
|
|
|
|
|
| 2/20/11 |
207785 |
|
1 |
|
|
7 |
|
|
| 3/1/11 |
207785 |
|
|
|
1 |
|
|
|
| 2/22/11 |
209566 |
1 |
|
|
|
|
|
1 |
| 2/22/11 |
209575 |
1 |
|
|
|
|
|
|
| 7/13/11 |
209575 |
|
2 |
|
|
|
|
|
| 8/13/11 |
209575 |
|
|
1 |
|
|
|
|
| 2/22/11 |
209577 |
1 |
|
|
|
|
|
|
| 5/1/11 |
209577 |
|
1 |
|
|
|
68 |
|
| 2/22/11 |
209636 |
1 |
|
|
|
|
|
|
| 5/9/11 |
209636 |
|
|
1 |
|
|
|
|
| 11/7/11 |
209636 |
|
|
2 |
|
|
|
|
| 2/23/11 |
209685 |
1 |
|
|
|
|
|
|
| 2/23/11 |
209694 |
1 |
|
|
|
|
|
|
| 9/13/11 |
209694 |
|
1 |
|
1 |
202 |
|
|
| 5/6/11 |
210567 |
1 |
|
|
|
|
|
1 |