HELP!!!! Count of Core Samples Available - Date Driven

Overview: Oil & Gas Industry: USGS is the place our company reps, mainly Geologists go to gather available core samples from wells that are drilled. Once they pick the cores they want they bring them back for analysis. Either in house experts examine them and make an analysis or it is sent out to a vendor example: Weatherford for their chemical analysis of the cores.

Maximum number to core samples allowed to be taken is 200! As long as you have 200 samples in your possession, and have not returned your analysis of the cores you cannot request any more samples. You have 6 months from the date you take them, to return the analysis. You give them a promise date of when you think you will have the analysis returned. Example: 100 samples are taken and not all are returned at the same time. Say, they only send back 50 analysis results as the other 50 are not completed yet, but will be. Multiple trips can be made as well with a few weeks or days but no matter what there cannot be anymore than 200 sample outs for the total of everyone. If one guy gets 100, then there is only 100 left for others to get. Once the count reaches 200. NO more can be taken till someone sends analysis back. Not all analysis is returned at the same time.

I have a countdown evaluation going in my worksheet counting down the days left to return analysis. In the Actual Date Analysis Returned, if a date is entered then the countdown stops for that Trip to gather core data. Also I have a notification Warning that says when countdown hits within 7 days "Sample Analysis is Due!!" if the countdown hits 0 then the remark is "Out of Compliance!!". There are penalties for not complying with returning analysis to the USGS on time. This is a 2 part request for help.

My request is I am having trouble completing my formulas. I can't figure out how to make the Sample Count Available formula to work correctly when not all the analysis is returned at the same time. Multiple people in multiple departments make trips to USGS. We had an issue where someone made a trip there only to find he could not get any samples as some had not been returned. NOT all 200 have to be returned at once. If you took 200 and returned 100, anyone can go there and take 10, 50, 5 or the remaining 100. (This is part One.) The second part which at this time is not a high priority but I want to do it if I can easily do it. And that is once the Day Countdown Evaluation hits 7 days till 0, then I want the worksheet warning to send an email to all the People that go to USGS to gathering core analysis giving them the Warning Message you got 7 days to comply else you are out of compliance. I am the Sr. Eng. Tech responsible for creating this workbook and I maintain the worksheets. And track the data. This is a Geo-Sample Data Tracking Workbook. All the Analysis results returned to us from the vendor is maintained in other databases/systems. (It is a lot of information!) I just tracking who's going, getting samples and sending them back on time.

Here are my columns: A=Date Sample Trip Planned, B=USGS Contact, C=Trip Number, D=Sample Repository, E=Company Rep, F=Beginning Samples Available at Start of Trip, G=Number of Samples Taken, H=Number of Samples Analysis Returned to USGS, I=Sample Count Available, J=Actual Date Taken, K=Promised Return Date, L=Actual Date Analysis Returned, M=Partial Sample Count Returned (Y/N), N=If Partial Sample Count Returned to USGS **Remainder** Sample Count to Return, O=Calculated Date that Analysis must be Returned to USGS, P=Day Countdown Evaluation, Q=Notification Warning.

A= input date, B=input name, C=Trip Number is unique example T1, T2, T3 and so on, D=input name, E=Company Rep=input name, F=trip #1 input of 200 as that's when we started this after that it's a formula, G=Number of Sample Taken is an input field, H=Number of Samples Analysis Returned to USGS is an input field, I= Sample Count Available is a formula, J=Actual Date Taken is an input field, K=Promised Date Returned is an input field, L=Actual Date Returned is an input field, M=Partial Sample Count Returned (Y/N) is an input field, N=If Partial Sample Count Returned to USGS **Remainder** Sample Count to Return is a formula, O=Calculated Date that Analysis must be returned to USGS is a formula, P=Day Countdown Evaluation is a formula, Q=Notification Warning is a formula.

This is what I need help with. I hope someone can help me figure this out. It worked great till I was told that they don't have to send back everything they took all at once! That messed up my formulas as now you have multiple people returning partial analysis. ALSO - Please NO VBA, I'm not that experienced in VBA yet. I'm getting there but not yet. PLEASE HELP. THANKYOU!!!!

Glenn were you able to see the worksheet I sent you? This site showed it attached, but you said sometimes this doesn't work so great on here. Just checking to see that you did receive it. - Jannie

Hi Sambo kid - Ok let me try again to post the attachment Sample Worksheet here. I know I sent one to Glenn but I thought I posted an attachment here as well. I'll try again. Ok I uploaded it I think. Can you tell me if you see it. Thanks.

JeteMc-Ok the problem I'm having is if someone returns only partial amts to USGS like lets say there was the Sally case 200 - 100 + 67 now available 167. Now Test comes along and takes 100 and only returns 50. There like needs to be like a 2nd return date column inserted as well for the Remainder samples returned. Col L = the actual date returned right, well that works great if you return all the samples you took. If you only return partials you have 6 months to return the rest. And I just thought of something else....what if the remainder like in Sally's case is 67 and let's say she returns 30 more within the 6 months, then that leaves 37 more she has left to return within the remainder of the 6 months. Boy howdy. This worksheet worked great until I was told they could return partial amts back. It's just gonna have to be multiple nested If clauses. Does that help?

Oops...Sallys not 67 but 33 left to return, lets say she returns 20, that would mean she has 13 left to return within the 6 month cycle. No matter what all sample analysis must be returned by Col M date.

A little confused.... Planned means this is the date they planned to go on their trip. It's simply a date field nothing more. The Samples Available = at the START of their trip this is how many samples there are available to get from USGS. Samples Taken = how many samples they took. Confusing because Trip 1 (T1) he starts with 200, he takes 75, which leaves 125 samples left for the next person to take. Keep in mind (T1) he has 6 months to return those samples!

So for Trip 2 (T2)...this person has available to them now 125 samples they can get at the start of their trip. Now (T1) person can return their sample analysis within 6 months so the samples available at the start of the next person's trip can change before or after or during their planned trip. If (T1) person returns their analysis prior, during or after (T2) person goes on their trip to USGS. (T1) planned to go 3/29/16 and usually they are gone for 2-3 days. They bring the samples back and then send them off for Analysis. It may take 2 weeks or a month for the analysis to come back from the vendor with their determination of the core samples.

So (T2) he is planned to go on his trip 5/18/16. So he leaves believing that the worksheet states he has 125 samples available he can get at the start of his trip. He only takes 100 samples. Which leaves 25 samples available for the next person. (T3) plans to go to USGS on 6/6/16. In the meantime (T1) gets his analysis back from the vendor and he then submits his results back to USGS on 6/1/16. So now instead of (T3) having 25 samples available at the start of their trip, they now have 100 available to them to take.

The actual date drivers are the Actual Date Taken and the Actual Date the Analysis is Returned and because there are people that only return PART of the samples I had to add an additional Partial Sample Count Returned Date for when they return the remainder of the samples they took. So (T3) they get their samples back from the vendor and only return 67 of them 9/6/16. Leaving 33 left to return by 12/6/16. As these samples are still out for analysis with a different vendor when they return them a date should enter into column Q. And when a date is entered into Column Q then the sample count in Column R should be added back to Samples Available to take. The worksheet is date driven and sample count driven.
The Promised Return Date = Just a date that when they left USGS they told them I promise to send back my analysis by this certain date ok. The Calculated Date is the Actual Date they have to send them back by. So they can promise all they want but it's just a date they gave USGS. They strive to meet their promise date, but, in reality it really doesn't matter if they meet this or not. They are bound by column M to return their findings. Column N is just a countdown telling them they have this many days left to respond. Notification Warning is a warning message... if column L isn't populated with a date, then once the countdown reaches 7 days left, I wanted the worksheet to generate an email warning to that person telling them You now have 7 days to get your analysis back to USGS.
I don't understand your column W. As you can't have dates less than the planned date???? I don't understand Column Y or Z they are not making sense in this case.

I reposted the worksheet and manually plugged in the figures as they should appear.

I've uploaded it like 3 times again now. Using Manage Attachments.

JeteMc - One thing I hadn't thought of till now and this is why I add the 2 columns The Partial Sample Count Returned Date (P) and Remainder of Partial Samples Left to Return (Q). In the cases where they only return in this example the 67 and left 33 to be sent later.... I had to add a column to track when the last 33 got sent back. This also affects the sample available count as well. So how do we tweak the formulas to reflect adding those 33 back into the mix.