Calculate date based on formula?

cainservicestn

Hi!

I have a "Transfer Log" that I am responsible for maintaining and I'm wondering if there is a formula I can use to help me determine a date. I have attached a screenshot with the relevant columns listed.

Basically, we have a tank that we put liquid into and take it out of. I have a formula which uses the "Container" and "Quantity" columns to calculate the quantity (in gallons) in Container "Frac1" at any given time. Here is that formula:

=SUMIF({Job Log Container}, "Frac1", {Job Log Gallons})

I can easily record the date that the liquid is put into the container("Date Arrived"), but I need a way to calculate the date it is removed ("Date Facility Left") using a First In, First Out Basis. I don't even know where to begin with something like this, so I am open to all ideas! Thank you in advance.

Jason Tarpinian

I'm not quite understanding everything you're trying to do, but it sounds like maybe SMALL(COLLECT()) could help you out? In my sample below, "Date" is when the material would've come to the facility, and "Date Left Facility" is when it's gone. So the formula below collects "Date" and returns the smallest (in date terms, smaller equals earlier in the past), while using COLLECT to only look at the rows where "Date Left Facility" is not a date. The "1" on the end says to choose the smallest date to meet your FIFO requirement, 2 would choose the 2nd smallest… and so on. If this isn't quite right let me know.

=SMALL(COLLECT(Date:Date, [Date Left Facility]:[Date Left Facility], NOT(ISDATE(@cell))), 1)