Date range formula

[Jaxs1111]

Hello, I need help in creating a formula under columns, D,E, and F that will bring in the values in column L which is hidden.

In example: Under Column BC July month
I would like a formula to Look at the dates in column J "Crew End Date"
and if the dates fall in between 7/1 and 7/31 then bring in the value in column L (which is hidden here) into cell BC and the corresponding
Obviously to add an error function where if dates are not entered in Crew End Date column then leave blank.

Hope this makes sense. The highlighted numbers below I typed in to show what I would like for the formula to show.

 

[Peter_SSs]

Welcome to the MrExcel board!

This would help in the future to save helpers having to manually type sample data.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


See if this helps. Note that my dates are in d/m/y format

22 07 11.xlsm
	J	K	L																																										BB	BC	BD	BE	BF
5																																														1/07/2022	1/08/2022	1/09/2022	1/10/2022
6																																														31/07/2022	31/08/2022	30/09/2022	31/10/2022
7	15/07/2022		1																																											1
8	25/08/2022		2																																												2
9	1/05/2022		3
10	1/07/2022		4																																											4
11	31/10/2022		5																																														5
Month Values

Cell Formulas
Range	Formula
BC7:BF11	BC7	=IF($J7=MEDIAN($J7,BC$5,BC$6),$L7,"")

 

[Alex Blakenburg]

Based on the headings provided, just using EOMonth would work as well.

20220711 If date put in column Jaxs1111.xlsx
	J	K	L	M																																									BB	BC	BD
5
6	Crew End Date	Substantial Completion	Col L Hidden																																											31/07/2022	31/08/2022
7
8
9	27/07/2022		10,000.00																																											10,000.00	-
10	25/08/2022		5,000.00																																											-	5,000.00
11			1,000.00																																											-	-
12
Sheet1

Cell Formulas
Range	Formula
BC9:BD11	BC9	=IF(EOMONTH($J9,0)=BC$6,$L9,0)

 

[Jaxs1111]

THANK YOU ALEX!! This worked!!!!! That helped!

What if I needed to take into consideration (Crew Start Date and End Date) and allocate the current month's revenue.
For example, The high-lighted dates have a revenue of 1341542.10. What is the best way to allocate the amount between the three columns?

 

[Alex Blakenburg]

See if this is what you had in mind:

20220711 If date put in column Jaxs1111.xlsx
	I	J	K	L	M																																									BB	BC	BD	BE	BF	BG	BH
5																																															1/07/2022	1/08/2022	1/09/2022	1/10/2022	1/11/2022
6	Crew Start Date	Crew End Date	Substantial Completion	Col L Hidden																																											31/07/2022	31/08/2022	30/09/2022	31/10/2022	30/11/2022	Total
7
8
9	10/07/2022	27/09/2022		10,000.00																																											2,658.23	3,924.05	3,417.72	-	-	10,000.00
10	10/07/2022	25/09/2022		5,000.00																																											1,363.64	2,012.99	1,623.38	-	-	5,000.00
11	5/08/2022	10/08/2022		1,000.00																																											-	1,000.00	-	-	-	1,000.00
12	10/07/2022	25/09/2022		15,000.00																																											4,090.91	6,038.96	4,870.13	-	-	15,000.00
Apportion

Cell Formulas
Range	Formula
BC5:BG5	BC5	=EOMONTH(BC6,-1)+1
BC9:BG12	BC9	=IF(AND($I9<>"",$J9<>""),$L9/($J9-$I9)*(BC$5=MEDIAN(EOMONTH($I9,-1)+1,EOMONTH($J9,0),BC$5))*(MIN($J9,EOMONTH(BC$5,0))-MAX($I9,BC$5-1)),0)
BH9:BH12	BH9	=SUM(BC9:BG9)

For an explanation see: https://www.myonlinetraininghub.com/excel-formula-to-spread-income-or-costs-over-months

 

[Jaxs1111]

THIS IS EXACTLY what I need! You guys are AMAZING!

 

[Alex Blakenburg]

Glad we could help. Thanks for the feedback.