Formula to take a value from a row and apply it to columns representing date ranges

[zoet88]

[FONT=&quot]Hi,[/FONT]
[FONT=&quot]I need some help with a formula in Excel. I have a column that shows a start date and a column that shows an end date, I have combined the start and end date to come up with a date range. Each of these date ranges show a cost within the date range.[/FONT]
[FONT=&quot]I need to create separate columns for each month and the cost that falls within that date range needs to go under each column. I have attached an image and highlighted the cells yellow where I would like to apply the formula. Is this possible?[/FONT]
[FONT=&quot]Sorry if I have not explained it well, let me know if I can clarify anything.[/FONT]
[FONT=&quot]https://imgur.com/a/LGVYFLz[/FONT]
[FONT=&quot]Thank you so much![/FONT]

 

[jtakw]

Ok, the way you converted the Text String to your Dates should have worked with my formula in Post # 7. So now, I'm wondering how you're entering the Month Columns manually, what Exactly do you Enter to get, say, January 2018 (as in F1 in my sample) ??

Besides that, it's actually rather easy to modify your LEFT, MID, and TEXT/DATE formulas to get Actual Date Values, as shown below, you no longer need the 2 Helper Columns ( 4 total ), and I've used my original formula in Post # 2 to obtain the results.

Again, if it doesn't work for you, please tell how you enter the Month Columns.
You Should, just type "January 2018" without quotes, and custom format that row as "mmmm yyyy" without quotes.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1						January 2018	February 2018	March 2018	April 2018	May 2018	June 2018	July 2018
2	20180300	March 2018	20180500	May 2018	55.68			55.68	55.68	55.68
3	20180200	February 2018	20180600	June 2018	76.34		76.34	76.34	76.34	76.34	76.34
4	20180600	June 2018	20180700	July 2018	99.99						99.99	99.99
5
6		Custom Format		Custom Format
7		mmmm yyyy		mmmm yyyy
Sheet404

Cell Formulas
Range	Formula
B2	=DATE(LEFT(A2,4),MID(A2,5,2),1)
D2	=DATE(LEFT(C2,4),MID(C2,5,2),1)
F2	=IF(AND(F$1>=$B2,F$1<=$D2),$E2,"")

 

[zoet88]

Thank you so much for your help - everything works now!

 

[jtakw]

You're welcome, welcome to the forum.

Glad it's working for you.