I am having problems trying to figure out a formula that has a time delay based on an entry, and adds values for only 12 months after that.
If someone in our organization is hired in Jan, we have a savings for that person that comes into play starting 6 months later so in July. On the same line item we will have 1 person hired in Feb, so that savings starts in Aug. The savings only runs for 12 months for each person hired.
So if I assume a 100.00 savings per person and I hire 1 person in Jan 2012 and 1 person in Feb 2012, my outcome should show 0 savings from Jan 2012 thru June 2012, a 100.00 savings in July 2012, a 200.00 savings from Aug 2012 thru June 2013, a 100.00 savings in July 2013, and 0 savings from Aug 2013 onwards.
I think this attached worksheet could satisfy your requirement. It looks quite complex at first because the formula needs to allow for a varying range length as the calculation moves to later months. Also, I have used a range to define the way the savings are achieved over time. This is flexible and you could easily have a different value in each month (perhaps ramping up and then declining rather than a sharp cut-off).
The OFFSET() function is used to control the range length. Whilst this provides flexibility, it is at a price of (a) complexity and (b) calculation speed. The latter is because Excel treats OFFSET as a volatile function and recalculates it even is the data it depends on has not altered.
The method I have used here is based on one I often apply to situations where, for example, income in a month depends both on the value billed in previous months and a payment profile.