# Formula Question (If? Offset?)

#### Gliffix101

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

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

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Replies
11
Views
480
Replies
1
Views
199
Replies
1
Views
140
Replies
0
Views
1K
Replies
6
Views
326

1,195,909
Messages
6,012,249
Members
441,687
Latest member
urimagic

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