Trouble with IF formula and comparing dates

[meganisagee]

Hi there,

I have a case management tool that identifies 6 different types of renewals by comparing submission month, day, and year between two different cells. D2 is the certification period end date (renewal date), and D4 is the date we received the renewal form. A previously suggested formula helped resolve many of the issues I was having at the time, but I just discovered another issue and can't figure out what is happening.

Here are the renewal types, based on an April 2023 renewal date:

1. Too Early: received 2 months prior to renewal date (February)
2. Early: received month prior to renewal date (March)
3. Timely: received month of renewal date, between 1st-15th (April 1st-15th)
4. Regular: received month of renewal date between 16th-end of month (April 16-30th)
5. Late: received month after renewal date (May 2023)
6. Too Late: received 2 months after renewal date (June 2023)

Although 5/30/2023 is correctly identified as a late renewal (#5), 5/31/2023 is being identified as "too late" (#6). Can someone help? Thanks!

 

[Fluff]

You need to use EDATE(B1,2)

 

[meganisagee]

Thanks for your response! When I update it to EOMONTH, it doesn't label 5/31/2023 as anything now:

 

[Fluff]

I realised I had made a mistake & edited my previous reply, have another look.

 

[meganisagee]

Thanks again! For whatever reason, it's still not identifying as anything:

But in fiddling with these adjustments, I can see that using the EDATE (start date, months) formula seems to be counting 30 days as the months part of it. Because in testing other dates, I can see that 3/31/2023 is labeled as a timely renewal instead of an early renewal. But 3/30 is labeled correctly, as "early"

Is there a formula that considers the months in their entirety as opposed to 30 days? (Hope that makes sense...?)

 

[Fluff]

Can you post your 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.

 

[meganisagee]

Thank you so much for this information! How cool. Here is my mini-sheet... hope it works as it should.

Application Due Dates v.3.xlsx
	A	B	C	D	E	F	G	H	I
1		4/30/2023
2	FIRST: Enter Cert. Period End Date			4/2023	Renewal Type:	EARLY
3
4	THEN: Enter Renewal Received Date			3/30/2023
5
SNAP Renewal

Cell Formulas
Range	Formula
B1	B1	=EOMONTH(D2,0)
F2	F2	=IF(OR(D2="",D4="",B1=""),"",IF(D4<=EDATE(B1,-2),"TOO EARLY. DUPLICATE!","")& IF(AND(D4>EDATE(B1,-2),D4<=EDATE(B1,-1)),"EARLY","")& IF(AND(D4>EDATE(B1,-1),D4<=DATE(YEAR(B1),MONTH(B1),15)),"TIMELY","")& IF(AND(D4>DATE(YEAR(B1),MONTH(B1),15),D4<=B1),"REGULAR","")& IF(AND(D4>B1,D4<=EDATE(B1,1)),"LATE","")& IF(D4>EDATE(B1,1),"TOO LATE. REAPPLY!",""))
E4	E4	=IF(AND(D4<>"",OR(WEEKDAY(D4,2)>5,COUNTIF(Holidays!$C$2:$BJ$14,D4))),"Note: Renewal date is a weekend/holiday.","")
F4	F4	=IF(F2="LATE","Is the LATE RENEWAL expedited?","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2	Expression	=EOMONTH(DATE(YEAR,MONTH,1),0)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D2:D3	Date	=1/1/1900
D4:D6	Date	between 1/1/1900 and 12/31/9999

 

[Fluff]

Thanks for that, how about

=IF(OR(D2="",D4="",B1=""),"",IF(D4<=EOMONTH(B1,-2),"TOO EARLY. DUPLICATE!","")&
IF(AND(D4>EOMONTH(B1,-2),D4<=EOMONTH(B1,-1)),"EARLY","")&
IF(AND(D4>EOMONTH(B1,-1),D4<=DATE(YEAR(B1),MONTH(B1),15)),"TIMELY","")&
IF(AND(D4>DATE(YEAR(B1),MONTH(B1),15),D4<=B1),"REGULAR","")&
IF(AND(D4>B1,D4<=EOMONTH(B1,1)),"LATE","")&
IF(D4>EOMONTH(B1,1),"TOO LATE. REAPPLY!",""))

 

[meganisagee]

You're awesome! This worked.

Thanks so much for your patience and expertise.

 

[Fluff]

You're welcome & thanks for the feedback.