Only Return a Value if it is Greater than Equal to Start Date, but stop when the End Date is Reached

[PWH1968]

Morning All

Today's query is: I have Sales figures that have occurred between Start and End Dates and what I want to show is which Month they Start and End on.

So in this particular scenario, some products (Rows 15 & 16 sales commenced Dec'22 and Jan'23, whereas rows 4-14 didn't start until Feb'23). ALL Sales ceased in Jan'25 so the formula needs to stop at column AB as column AC onwards is not applicable) please see below as this explanation is probably rubbish.

ONS-BOS Levy Calculator.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1		ANTICIPATED TCV
2			Y1												Y2												Y3
3	SKU	PILLAR	1-Dec-2022	1-Jan-2023	1-Feb-2023	1-Mar-2023	1-Apr-2023	1-May-2023	1-Jun-2023	1-Jul-2023	1-Aug-2023	1-Sep-2023	1-Oct-2023	1-Nov-2023	1-Dec-2023	1-Jan-2024	1-Feb-2024	1-Mar-2024	1-Apr-2024	1-May-2024	1-Jun-2024	1-Jul-2024	1-Aug-2024	1-Sep-2024	1-Oct-2024	1-Nov-2024	1-Dec-2024	1-Jan-2025	1-Feb-2025	1-Mar-2025	1-Apr-2025	1-May-2025	1-Jun-2025	1-Jul-2025	1-Aug-2025	1-Sep-2025	1-Oct-2025	1-Nov-2025
4	B91079	ERP			$ 4,243	$ 4,243	$ 4,243	$ 4,243	$ 4,243	$ 4,243	$ 4,243	$ 4,243	$ 4,243	$ 4,243	$ 4,243	$ 4,243	$ 4,243	$ 4,243	$ 4,243	$ 4,243	$ 4,243	$ 4,243	$ 4,243	$ 4,243	$ 4,243	$ 4,243	$ 4,243	$ 4,243
5	B91082	ERP			$ 5,391	$ 5,391	$ 5,391	$ 5,391	$ 5,391	$ 5,391	$ 5,391	$ 5,391	$ 5,391	$ 5,391	$ 5,391	$ 5,391	$ 5,391	$ 5,391	$ 5,391	$ 5,391	$ 5,391	$ 5,391	$ 5,391	$ 5,391	$ 5,391	$ 5,391	$ 5,391	$ 5,391
6	B91080	ERP			$ 12,268	$ 12,268	$ 12,268	$ 12,268	$ 12,268	$ 12,268	$ 12,268	$ 12,268	$ 12,268	$ 12,268	$ 12,268	$ 12,268	$ 12,268	$ 12,268	$ 12,268	$ 12,268	$ 12,268	$ 12,268	$ 12,268	$ 12,268	$ 12,268	$ 12,268	$ 12,268	$ 12,268
7	B91083	ERP			$ 7,361	$ 7,361	$ 7,361	$ 7,361	$ 7,361	$ 7,361	$ 7,361	$ 7,361	$ 7,361	$ 7,361	$ 7,361	$ 7,361	$ 7,361	$ 7,361	$ 7,361	$ 7,361	$ 7,361	$ 7,361	$ 7,361	$ 7,361	$ 7,361	$ 7,361	$ 7,361	$ 7,361
8	B85800	HCM			$ 14,568	$ 14,568	$ 14,568	$ 14,568	$ 14,568	$ 14,568	$ 14,568	$ 14,568	$ 14,568	$ 14,568	$ 14,568	$ 14,568	$ 14,568	$ 14,568	$ 14,568	$ 14,568	$ 14,568	$ 14,568	$ 14,568	$ 14,568	$ 14,568	$ 14,568	$ 14,568	$ 14,568
9	B75365	HCM			$ 2,914	$ 2,914	$ 2,914	$ 2,914	$ 2,914	$ 2,914	$ 2,914	$ 2,914	$ 2,914	$ 2,914	$ 2,914	$ 2,914	$ 2,914	$ 2,914	$ 2,914	$ 2,914	$ 2,914	$ 2,914	$ 2,914	$ 2,914	$ 2,914	$ 2,914	$ 2,914	$ 2,914
10	B84490	HCM			$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248
11	B84490	HCM			$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248
12	B84490	HCM			$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248	$ 1,248
13	B87368	HCM			$ 2,496	$ 2,496	$ 2,496	$ 2,496	$ 2,496	$ 2,496	$ 2,496	$ 2,496	$ 2,496	$ 2,496	$ 2,496	$ 2,496	$ 2,496	$ 2,496	$ 2,496	$ 2,496	$ 2,496	$ 2,496	$ 2,496	$ 2,496	$ 2,496	$ 2,496	$ 2,496	$ 2,496
14	B94925	HCM			$ 6,799	$ 6,799	$ 6,799	$ 6,799	$ 6,799	$ 6,799	$ 6,799	$ 6,799	$ 6,799	$ 6,799	$ 6,799	$ 6,799	$ 6,799	$ 6,799	$ 6,799	$ 6,799	$ 6,799	$ 6,799	$ 6,799	$ 6,799	$ 6,799	$ 6,799	$ 6,799	$ 6,799
15	B73946	EPM	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629	$ 629
16	B79785	EPM	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634	$ 634
17			$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
18			$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
Formula Calculations

Cell Formulas
Range	Formula
C3	C3	=+MIN(BOS_Levy_Calculator[Start Date])
D3:AL3	D3	=+EDATE(C3,1)
A4:A203	A4	=+IF(BOS_Levy_Calculator[SKU '#]=+"","",BOS_Levy_Calculator[SKU '#])
B4:B203	B4	=+IF(BOS_Levy_Calculator[Pillar]=+"","",BOS_Levy_Calculator[Pillar])
C4:AB203	C4	=+IF(C$3>=+BOS_Levy_Calculator[Start Date],BOS_Levy_Calculator[Actual Monthly Charge],"")
AC4:AL203	AC4	=+IF(AC$3<=+BOS_Levy_Calculator[End Date],BOS_Levy_Calculator[Actual Monthly Charge],"")
Dynamic array formulas.

 

[jdellasala]

It's great that you used XL2BB to post the data, but I had to use Values & Formats only to copy it because the formulas were a mess as "BOS_Levy_Calculator" isn't there,
Also, using Merge & Center is a great way to screw up a worksheet's structure. Instead use Center Across Selection under Format Cells -> Alignment -> Horizontal drop down (and I recommend putting a thin outside border with the selection too as there's no indication of what cells are being used for the centering).

 

[PWH1968]

Thanks for the formatting tips but what about my actual question - do you have a solution please?