Formula Question (If? Offset?)

[Gliffix101]

Hello Forum,

Coming to you for some help in building out a formula. I have a vague idea of what I need it to do, but just can't seem to get the final touches on.

I have attached a copy of the sample below. I need to calculate the time "Status Name Date" between an End (v_End) and a Beginning (v_Start). The problem is that there are, at times, cases where an item (Status Name) does not have a start. I need a formula that will go to a "Yes" in v_End, and then look backwards (Up) until it finds the Start (v_Start) and then calculate the difference between the two. The formula should error out if it comes across another End OR no start.

Here is the formula I started using, but this only does an offset of -1. There are cases where the offset may need to be -2 or greater. I also have cases such as the first Don't Hide (Row 4) that has no beginning, so I would need it to reflect #N/A. The ideal example is that

=IF(I6="Yes",DAYS(F6,OFFSET(K6,-1,-5,1)) & " Days",0)

Some examples that should work:

1) I4 (v_End) would look back and see there is no start, returning "N/A" in a new column (Column K)
2) I6 (v_End) would look back and see a start in H5 (v_Start) and would determine the difference in dates between (F6 vs. F5)
3) I10 (v_End) would look back and see a start in H8 (v_Start) and would determine the difference in dates between (F10 vs. F8)
4) I14 (v_End) would look back and see a start in H12 (v_Start) and would determine the difference in dates between (F14 vs. F12)
5) I16 (v_End) would look back and see an end (I14) before a start (H12) and would return (N/A)
6) I22 (v_End) would look back and see an end (I20) before a start (H19) and would return (N/A)

	A	B	C	D	E	F	G	H	I	J
1	Acct #	Type	Status Name	Status Name History	Status Name User	Status Name Date	v_User	v_Start	v_End	v_Hide
2	-	FD	Haz	Outstanding	Web User	8/6/2015	N/A	No	No	Hide
3	-	FD	Haz	Requested	Web User 2	11/17/2016	CompA	No	No	Hide
4	-	FD	Haz	Received	Web User 2	11/17/2016	CompA	No	Yes	Don't Hide
5	-	SL	Haz	Outstanding	Web User	1/28/2016	N/A	Yes	No	Don't Hide
6	-	SL	Haz	Problem	Web User 2	11/2/2016	CompB	No	Yes	Don't Hide
7	-	FD	Request45T	Outstanding	Web User	4/13/2016	N/A	No	No	Hide
8	-	FD	Request45T	Initiated	Web User 2	10/28/2016	N/A	Yes	No	Don't Hide
9	-	FD	Request45T	Requested	Web User 2	11/2/2016	CompA	No	No	Hide
10	-	FD	Request45T	Completed	Web User 2	11/9/2016	CompA	No	Yes	Don't Hide
11	-	FD	Haz	Outstanding	Web User	4/13/2016	N/A	No	No	Hide
12	-	FD	Haz	Initiated	Web User 2	11/11/2016	N/A	Yes	No	Don't Hide
13	-	FD	Haz	Requested	Web User 2	11/14/2016	CompA	No	No	Hide
14	-	FD	Haz	Received	Web User 2	11/16/2016	CompA	No	Yes	Don't Hide
15	-	SL	InitVV	Outstanding	Web User	4/22/2016	N/A	No	No	Hide
16	-	SL	InitVV	Completed	Web User 2	11/21/2016	CompA	No	Yes	Don't Hide
17	-	SL	InitVV	Outstanding	Web User	5/3/2016	N/A	No	No	Hide
18	-	SL	InitVV	Not Applicable	Web User 2	5/16/2016	N/A	No	No	Hide
19	-	SL	InitVV	Initiated	Web User 2	6/20/2016	N/A	Yes	No	Don't Hide
20	-	SL	InitVV	Completed	Web User 2	6/21/2016	CompA	No	Yes	Don't Hide
21	-	SL	InitVV	Requested	Web User 2	6/21/2016	CompA	No	No	Hide
22	-	SL	InitVV	Completed	Web User 2	6/21/2016	CompA	No	Yes	Don't Hide
23	-	SL	InitVV	Initiated	Web User 2	6/28/2016	N/A	Yes	No	Don't Hide
24	-	SL	InitVV	Completed	Web User 2	6/28/2016	CompA	No	Yes	Don't Hide
25	-	SL	InitVV	Expired	Web User 2	8/18/2016	N/A	No	No	Hide
26	-	SL	InitVV	Initiated	Web User 2	8/18/2016	N/A	Yes	No	Don't Hide
27	-	SL	InitVV	Requested	Web User 2	8/23/2016	CompA	No	No	Hide
28	-	SL	InitVV	Completed	Web User 2	8/23/2016	CompA	No	Yes	Don't Hide
29	-	SL	InitVV	Returned	Web User 2	9/12/2016	N/A	No	No	Hide
30	-	SL	InitVV	Initiated	Web User 2	9/14/2016	N/A	Yes	No	Don't Hide
31	-	SL	InitVV	Requested	Web User 2	9/15/2016	CompA	No	No	Hide
32	-	SL	InitVV	Completed	Web User 2	9/28/2016	CompA	No	Yes	Don't Hide
33	-	SL	InitVV	Initiated	Web User 2	11/11/2016	N/A	Yes	No	Don't Hide
34	-	SL	InitVV	Requested	Web User 2	11/14/2016	CompA	No	No	Hide
35	-	SL	InitVV	Completed	Web User 2	11/14/2016	CompA	No	Yes	Don't Hide
36	-	FD	InitVV	Outstanding	Web User	5/4/2016	N/A	No	No	Hide
37	-	FD	InitVV	Initiated	Web User 2	5/23/2016	N/A	Yes	No	Don't Hide
38	-	FD	InitVV	Requested	Web User 2	5/24/2016	CompA	No	No	Hide
39	-	FD	InitVV	Completed	Web User 2	5/24/2016	CompA	No	Yes	Don't Hide
40	-	FD	InitVV	Initiated	Web User 2	9/15/2016	N/A	Yes	No	Don't Hide
41	-	FD	InitVV	Requested	Web User 2	9/19/2016	CompA	No	No	Hide
42	-	FD	InitVV	Completed	Web User 2	9/20/2016	CompA	No	Yes	Don't Hide
43	-	FD	InitVV	Initiated	Web User 2	11/2/2016	N/A	Yes	No	Don't Hide
44	-	FD	InitVV	Requested	Web User 2	11/3/2016	CompA	No	No	Hide
45	-	FD	InitVV	Completed	Web User 2	11/7/2016	CompA	No	Yes	Don't Hide

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Please ask any questions you may have. I looked through and did not see a way to add an attachment to post my sample document.

Thanks,

Bill