Formula that provides answer based on date

[chipgiii]

Hi,

I have in column C an amount. In Column D a start date. As headers across columns E1 to AB1 I have dates (9/1/2018, 10/1/2018, 11/1/2018....). I am seeking a formula that looks at the start date in column D, and starts the calculation the same month and year in the header row (E1 to AB1), and repeats the value for 12 months.

I thought I saw a way to do this using offset, but can't remember.




Any help is welcomed!

 

[Special-K99]

Maybe

in E2
=IF(E$1=$D2,$C2,"")
and copy across and presumably down for other amounts in column C that relate to the same date headers.

 

[chipgiii]

Not sure if you can see this, but this is what I want to result:
[TABLE="width: 1471"]
<colgroup><col><col><col><col><col span="3"><col span="9"><col span="3"><col span="3"></colgroup><tbody>[TR]
[TD]Site[/TD]
[TD]Savings Amount[/TD]
[TD]Savings Start Date[/TD]
[TD="align: right"]9/1/2018[/TD]
[TD="align: right"]10/1/2018[/TD]
[TD="align: right"]11/1/2018[/TD]
[TD="align: right"]12/1/2018[/TD]
[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]2/1/2019[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]4/1/2019[/TD]
[TD="align: right"]5/1/2019[/TD]
[TD="align: right"]6/1/2019[/TD]
[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]9/1/2019[/TD]
[TD="align: right"]10/1/2019[/TD]
[TD="align: right"]11/1/2019[/TD]
[TD="align: right"]12/1/2019[/TD]
[TD="align: right"]1/1/2020[/TD]
[TD="align: right"]2/1/2020[/TD]
[TD="align: right"]3/1/2020[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]11/2/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]8333.3[/TD]
[TD="align: right"]8333[/TD]
[TD="align: right"]8333[/TD]
[TD="align: right"]8333[/TD]
[TD="align: right"]8333[/TD]
[TD="align: right"]8333[/TD]
[TD="align: right"]8333[/TD]
[TD="align: right"]8333[/TD]
[TD="align: right"]8333[/TD]
[TD="align: right"]8333[/TD]
[TD="align: right"]8333[/TD]
[TD="align: right"]8333[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]12/1/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]417[/TD]
[TD="align: right"]417[/TD]
[TD="align: right"]417[/TD]
[TD="align: right"]417[/TD]
[TD="align: right"]417[/TD]
[TD="align: right"]417[/TD]
[TD="align: right"]417[/TD]
[TD="align: right"]417[/TD]
[TD="align: right"]417[/TD]
[TD="align: right"]417[/TD]
[TD="align: right"]417[/TD]
[TD="align: right"]417[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]h[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]4/5/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2083[/TD]
[TD="align: right"]2083[/TD]
[TD="align: right"]2083[/TD]
[TD="align: right"]2083[/TD]
[TD="align: right"]2083[/TD]
[TD="align: right"]2083[/TD]
[TD="align: right"]2083[/TD]
[TD="align: right"]2083[/TD]
[TD="align: right"]2083[/TD]
[TD="align: right"]2083[/TD]
[TD="align: right"]2083[/TD]
[TD="align: right"]2083[/TD]
[/TR]
</tbody>[/TABLE]

 

[chipgiii]

Figured it out: =IFERROR(IF(H$1=MEDIAN(EOMONTH($D2,-1)+1,EOMONTH($E2,0),H$1)=TRUE,$C2/12,""),"")

Actually figured part of it out with a lot of help!

Thanks for giving it a go.