Formula that provides answer based on date

chipgiii

Board Regular
Joined
Nov 24, 2008
Messages
136
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!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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.
 
Upvote 0
Not sure if you can see this, but this is what I want to result:
SiteSavings AmountSavings Start Date9/1/201810/1/201811/1/201812/1/20181/1/20192/1/20193/1/20194/1/20195/1/20196/1/20197/1/20198/1/20199/1/201910/1/201911/1/201912/1/20191/1/20202/1/20203/1/2020
d10000011/2/2018 8333.383338333833383338333833383338333833383338333
e500012/1/2018 417417417417417417417417417417417417
h250004/5/2019 208320832083208320832083208320832083208320832083

<colgroup><col><col><col><col><col span="3"><col span="9"><col span="3"><col span="3"></colgroup><tbody>
</tbody>
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top