I can't figure out how to track years

[shawnjohn]

I'm creating an inventory/invoice sheet for a business that inspects and tests fire extinguishers. I've already made it so that I can put in an extinguishers manufacture date, which then auto populates a cell that shows when I need to do a 6 yr test and another cell that shows when I need to do a 12 year test. What I can't figure out is how to auto update this year to year. I keep these forms for each customer, and I provide them with a summary each year, telling them how many of each test that needs to be done this year and the next year so that they can budget for the upcoming costs. To this point I've been doing this manually and I'd like to have it done automatically if possible.

I'll give a short example in case anyone would like it.

Manufacture date 6 year test 12 year test
D1=2008 F1=2014 H1=2020
D2=2008 F2=2014 H2=2020
D3=2009 F3=2015 H3=2021
D4=2002 F4=2008 H4=2014
D5=2003 F5=2009 H5=2015

In my summary, I would like it to auto populate to tell me that I need to do 2 "6 year tests" and 1 "12 year test" and that next year I'll need to do 1 "6 year test" and 1 "12 year test". Then when 2019 rolls around it lets me know that I have x number of tests to do in 2020.

Thanks in advance for any and all help.

Shawn

[humdingaling]

please upload spreadsheet with data input and expected outputs you want to see

[dubcap01]

Hi shawnjohn

A countif will do the job, please see example spreadsheet, results on Sheet2

[shawnjohn]

QFS.xlsx

I would like to be able to put the extinguishers manufacture date into column c, which will then auto populate columns H and I on the "Data Entry" sheet. Which I currently have done, although it might not be the best/correct way. The problem I'm having is figuring out how to update theses dates annually and put them in the "Totals" sheet. When I print the "Totals" page, I would like it to be filled automatically with the information to tell the customer how many 6yr are being done this year and how many are being done next year. The same with the hydros (12yr). So when go see a customer in 2018, and that customer has 3 6yr and 5 hydros due in 2019, it will show in the totals page automatically as well as the tests that I'm doing in 2018.

I'm not sure if I'm explaining it very good, but I hope so.

Thanks again, Shawn