Nested If Statement dealing with dates between multiple date ranges

[ljmexcel]

Hi all, I'm hoping someone can help with this.

• I have a 105,000 row spreadsheet with each row representing a 5 minute interval on a given date.
• The date range spans a 12 month period.
• As per the attached file 'bill date range, a bill is produced each month and this bill a date range from the 9th of the start month to the 8th of the end month.
• For each date in each row, I want it to look through all bill start and end dates to return the bill month on column AF.
• I have added this formula =IF(AND(O3>=$AG$3,O3<=$AH$3),$AF$3,0).
• The formula above looks at the date in the row(O3 and checked that it is greater than or equal to the March Bill start Date and less that or equal to the March Bill end date and then returns 'Mar' as the bill month. It is only checking the March bill date ranges
• I need to nest this formula so for each row, it looks through all the Bill start and end dates (e.g. Mar through to Jan) so it can identify the correct month.
• I can't see to figure this out.
• Would love some help if possible. Maybe there is a better way and how I have started does make sense.

Much appreciated - John

 

[awoohaw]

Take your date and subtract 8 days. Then format as "mmm".

 

[ljmexcel]

Take your date and subtract 8 days. Then format as "mmm".

Thanks you for the response, but I don't quite understand it. If I pick one date (11/02/2022), it should be flagged as a march Bill because it falls in the March Bill date range of 8/2 - 9/3 2022.
Iff I apply the suggested solution I subtract 8 days from 11/2/22 which results in 3/2/22. If I format that as 'mmm' it returns Feb. The correct result should be Mar.
Note also that the start and end ranges will vary, use case from use case.

 

[awoohaw]

hold on.. i'll post my worksheet. But they are in US Regional settings.

 

[awoohaw]

How about this:
Don't worry about the calculated day for >=9 being the 1st of a month. It is just to get the month calculated, the day makes no difference in the final result.

mr excel questions 5.xlsm
	A	B	C	D	E	F	G
16	US (MM/DD/YYYY)	if Day >=9 Next month name, otherwise current month name.	date format: mmm (Month name abreviated)	8 Days Earlier plus one month calculation	DD/MM/YYYY	DD/MM/YYYY if Day >=9 Next month name, otherwise current month name.	date format of mmm
17	01/07/2023	01/07/2023	Jan	=IF(DAY(A17)>=9,EOMONTH(A17,0)+1,A17)	07/01/2023	07/01/2023	Jan
18	01/08/2023	01/08/2023	Jan	=IF(DAY(A18)>=9,EOMONTH(A18,0)+1,A18)	08/01/2023	08/01/2023	Jan
19	01/09/2029	02/01/2029	Feb	=IF(DAY(A19)>=9,EOMONTH(A19,0)+1,A19)	09/01/2029	01/02/2029	Feb
20	01/10/2023	02/01/2023	Feb	=IF(DAY(A20)>=9,EOMONTH(A20,0)+1,A20)	10/01/2023	01/02/2023	Feb
21	01/11/2023	02/01/2023	Feb	=IF(DAY(A21)>=9,EOMONTH(A21,0)+1,A21)	11/01/2023	01/02/2023	Feb
22	02/06/2023	02/06/2023	Feb	=IF(DAY(A22)>=9,EOMONTH(A22,0)+1,A22)	06/02/2023	06/02/2023	Feb
23	02/07/2023	02/07/2023	Feb	=IF(DAY(A23)>=9,EOMONTH(A23,0)+1,A23)	07/02/2023	07/02/2023	Feb
24	02/08/2023	02/08/2023	Feb	=IF(DAY(A24)>=9,EOMONTH(A24,0)+1,A24)	08/02/2023	08/02/2023	Feb
25	02/09/2029	03/01/2029	Mar	=IF(DAY(A25)>=9,EOMONTH(A25,0)+1,A25)	09/02/2029	01/03/2029	Mar
26	02/10/2029	03/01/2029	Mar	=IF(DAY(A26)>=9,EOMONTH(A26,0)+1,A26)	10/02/2029	01/03/2029	Mar
27	02/11/2029	03/01/2029	Mar	=IF(DAY(A27)>=9,EOMONTH(A27,0)+1,A27)	11/02/2029	01/03/2029	Mar
28	02/12/2029	03/01/2029	Mar	=IF(DAY(A28)>=9,EOMONTH(A28,0)+1,A28)	12/02/2029	01/03/2029	Mar
29	02/13/2029	03/01/2029	Mar	=IF(DAY(A29)>=9,EOMONTH(A29,0)+1,A29)	13/02/2029	01/03/2029	Mar
Group Bill Months

Cell Formulas
Range	Formula
B17:B29	B17	=IF(DAY(A17)>=9,EOMONTH(A17,0)+1,A17)
C17:C29	C17	=IF(DAY(A17)>=9,EOMONTH(A17,0)+1,A17)
D17:D29	D17	=FORMULATEXT(C17)
E17:E29	E17	=A17
F17:F29	F17	=IF(DAY(A17)>=9,EOMONTH(A17,0)+1,A17)
G17:G29	G17	=F17

 

[awoohaw]

And if you want to use only the text value of a month name then use

=TEXT(IF(DAY(A17)>=9,EOMONTH(A17,0)+1,A17),"mmm")

This difference in the two calculations is that I thought your months were going backward, not forward.

 

[ljmexcel]

Thank you SO much @awoohaw . Based on the ideas you gave me, I did this =IF(DAY(O3)>=$AD$3,EOMONTH(O3,0)+1,O3). AD3 is simply the starting date for each billing month

 

[awoohaw]

Happy to help! Best wishes. Welcome to the forum.