Finding invoice month based on periodic dates

[GarryFreeman]

Hello, I am hoping this is easy for someone but I cannot get my head around it.

My customer works in periods that are not calendar months but I need to report / forecast them in my own business in the calendar months.
I have managed to total the value using sumif but I am looking for a formula that will just populate the month based on the date ranges.

Using the example below the first line should return the month July as it falls between the customers dates of 25/06 and 22/07, however the second would fall into August as all sales between 23/07 and 19/08 from the customer are reported in their August figures.

Is there a simple formula that I could use to quickly reference what sales are falling into what months when they send me their orders based on expected delivery date (in the future)
For this I could assume that the below starts (QTY) in cell A1

Thanks in advance

Garry

Qty	Due Date	Invoice Month			Starts	Ends
5​	12/07/2021​			January	01/01/2021	21/01/2021
29​	30/07/2021​			February	22/01/2021	18/02/2021
26​	30/07/2021​			March	19/02/2021	25/03/2021
10​	30/07/2021​			April	26/03/2021	22/04/2021
13​	30/07/2021​			May	23/04/2021	20/05/2021
11​	30/07/2021​			June	21/05/2021	24/06/2021
32​	02/08/2021​			July	25/06/2021	22/07/2021
12​	02/08/2021​			August	23/07/2021	19/08/2021
14​	02/08/2021​			September	20/08/2021	23/09/2021
250​	03/08/2021​			October	24/09/2021	21/10/2021
300​	03/08/2021​			November	22/10/2021	18/11/2021
20​	23/08/2021​			December	19/11/2021	23/12/2021
249​	23/08/2021​
5​	23/08/2021​
4​	23/08/2021​
22​	08/11/2021​
308​	08/11/2021​
4​	08/11/2021​
2​	08/11/2021​

 

[Crystalyzer]

Try this formula

=INDEX($F$2:$F$13,MATCH(B2,$E$2:$E$13,-1))

Note you will need a lookup table as in E1:F13 below sorted in descending date order.

Book2
	A	B	C	D	E	F
1	Qty	Due Date	Invoice Month		End Date	Month
2	5	12/07/2021	July		23/12/2021	December
3	29	30/07/2021	August		15/11/2021	November
4	26	02/08/2021	August		21/10/2021	October
5	10	03/08/2021	August		23/09/2021	September
6	13	23/08/2021	September		19/08/2021	August
7	11	08/11/2021	November		22/07/2021	July
8					24/06/2021	June
9					20/05/2021	May
10					22/04/2021	April
11					25/03/2021	March
12					18/02/2021	February
13					21/01/2021	January
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=INDEX($F$2:$F$13,MATCH(B2,$E$2:$E$13,-1))

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	Qty	Due Date	Invoice Month					Starts	Ends
2	5	12/07/2021	July	January	0		January	01/01/2021	21/01/2021
3	29	30/07/2021	August	February	0		February	22/01/2021	18/02/2021
4	26	30/07/2021	August	March	0		March	19/02/2021	25/03/2021
5	10	30/07/2021	August	April	0		April	26/03/2021	22/04/2021
6	13	30/07/2021	August	May	0		May	23/04/2021	20/05/2021
7	11	30/07/2021	August	June	0		June	21/05/2021	24/06/2021
8	32	02/08/2021	August	July	5		July	25/06/2021	22/07/2021
9	12	02/08/2021	August	August	697		August	23/07/2021	19/08/2021
10	14	02/08/2021	August	September	278		September	20/08/2021	23/09/2021
11	250	03/08/2021	August	October	0		October	24/09/2021	21/10/2021
12	300	03/08/2021	August	November	336		November	22/10/2021	18/11/2021
13	20	23/08/2021	September	December	0		December	19/11/2021	23/12/2021
14	249	23/08/2021	September
15	5	23/08/2021	September
16	4	23/08/2021	September
17	22	08/11/2021	November
18	308	08/11/2021	November
19	4	08/11/2021	November
20	2	08/11/2021	November
21
Reports

Cell Formulas
Range	Formula
E2:E13	E2	=SUMIFS(A:A,B:B,">="&H2,B:B,"<="&I2)
C2:C20	C2	=XLOOKUP(B2,$H$2:$H$13,$G$2:$G$13,"",-1)

 

[GarryFreeman]

@Crystalizer, @Fluff

Thank you to you both for your solutions, they both work perfectly for my needs

Regards

Garry

 

[Fluff]

Glad we could help & thanks for the feedback.