So I've been tasked to make a sheet that can pull an inventory number at the beginning of each year based on expiration dates. For example, the base year is 2013, and I have a certain number of gallons of milk from one source that all expire 5/1/2020 (implausible, I know). Then from another source I have 6 gallons that expire 4/30/2018, and so on. Does anyone have an idea of how to write a formula that would tell me how many gallons are good at the beginning of each year? So for year 1 (2013) it would be all of the milk, then at the beginning of 2014 it would be all the milk minus those that expired in 2013, etc. I have attached an example spreadsheet for consideration.
example1.xlsx
Bookmarks