Seemingly Complicated Formula for a timeline

**achenbachwb** · 08-01-2012, 09:47 AM

I'm trying to write a formula that will automatically fill in a time line based on the date you enter into a cell and a 24 month generic period of time. For example you would have 24 months across the top in your columns. Down the first row you would have a bunch of costs that would have different start dates based on when the work would start. I want month one to be the date you have listed as the start date and them all other months to be based off of that month so that I can load it into a calendar. I've attached a very simple example. I've manually put the numbers into the proper positions and highlighted the cells in yellow. I'm looking for a formula to do it automatically so as I change the dates it automatically populates the calendar.

Thanks

**baldphil** · 08-01-2012, 10:41 AM

Here is a formula that seems to work (This formula appears in cell AF7 in the attached file):

=IFERROR(HLOOKUP(12*(YEAR(AF$5)-YEAR($E7))+(MONTH(AF$5)-MONTH($E7))+1, $F$5:$AC$10, ROW()-ROW(AF$5)+1, FALSE), 0)

I have added conditional formatting to highlight nonzero cells.

Sample%20Timeline(1).xlsx