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]

Hi,

Why is the 3rd row for January 2018 populated with $76.34 in your sample?

Assuming the Start and End dates in A2:B3 is the 1st of the respective month:

Book1
	A	B	C	D	E	F	G	H
1					January 2018	February 2018	March 2018	April 2018
2	Jan-18	Feb-18		55.68	55.68	55.68
3	Feb-18	Apr-18		76.34		76.34	76.34	76.34
Sheet404

Cell Formulas
Range	Formula
E2	=IF(AND(E$1>=$A2,E$1<=$B2),$D2,"")

E2 formula copied down and across.

 

[Kamolga]

Hi,

If your first cell is a1, the first yellow cell is in e2 and works with following

=IF(AND(E$1>=$A2,E$1<=$B2),$D2,"")

You can then drag it

 

[zoet88]

Apologies, I typed it in manually but you are correct - the third row for January 2018 should remain blank.

Thank you!
This formula seems to only work for the first row e.g. in other rows the January 2018 column is being filled out when the start date is May 2018.
I thought this could have been due to different formatting so I changed the column labels to a format of 'short date' and formatted the 'start date' and 'end date' columns as 'short date' too but this has not resolved it.

Do you have any ideas?

 

[jtakw]

As I pointed out.

Assuming the Start and End dates in A2:B3 is the 1st of the respective month:

This Also goes for E1:E##

How Exactly do you input the Dates in A2:B3 And E1:E## ?

 

[zoet88]

Initially, in the spreadsheet the start and end dates were shown as numbers as follows: 20180500 (meaning 2018 May). Only a month and year were stated, no days.
I was told to get the start and end dates into the format 'May 2018' which I have done. I searched online how to do this and I may have done it in a very long way but I firstly used a 'LEFT' column on 20180500 to get the year and this was inserted into a column. To get the month I used a 'MID' formula to extract the number e.g. 05 and this was inserted into another column and then a 'TEXTDATE' formula was used to convert 05 to May. To combine 2018 and May into May 2018 I used another 'TEXTDATE' formula. So this is how I got to column A and B for start and end dates.

The months in E1:E## were inserted manually as I have to show the cost under every month between the date ranges and this is where I am struggling to apply a formula.

 

[jtakw]

Ok, so the Start and End dates are Not Real Date values, but rather Text.

Unless you want help to convert the original strings like "20180500" to Real Date values, see if this "workaround" gives you what you want:

Book1
	A	B	C	D	E	F	G	H
1					January 2018	February 2018	March 2018	April 2018
2	Jan-18	Feb-18		55.68	55.68	55.68
3	Feb-18	Apr-18		76.34		76.34	76.34	76.34
Sheet404

Cell Formulas
Range	Formula
E2	=IF(AND(E$1>=EOMONTH($A2,-1)+1,E$1<=EOMONTH($B2,-1)+1),$D2,"")

 

[zoet88]

Thank you - I have applied the formula but unfortunately every cell is blank. Maybe the last thing I could try is to convert the original dates '20180500' to real date values and see if the initial formula you suggested works for all cells?

 

[jtakw]

Ok, that's probably the best way to go.

Can you show a few (not just one) samples of possible Text strings for the Start and End Dates, wouldn't hurt if you Also show the formulas you're currently using to Convert them.

 

[zoet88]

Sure, I have attached an image here: https://imgur.com/a/Pu0UjYu
The start date and end date in the white cells is the original text. The green columns after the start date and end date show the formulas I have applied.
The month columns after the 'amount' column have been inputted manually and the yellow cells show where I need to apply a formula to insert the amount if it falls within the month.