Trouble with IF Formula & Comparing Dates

[meganisagee]

Hi everyone,

I am working on a case management tool and am trying to identify 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. I have all but one of them working, and after a few hours of troubleshooting, I've come for help.

Here are the renewal types, based on a 12/2022 renewal date:

1. Too Early: received 2 months prior to renewal date (10/2022)
2. Early: received month prior to renewal date (11/2022)
3. Timely: received month of renewal date, between 1st-15th (12/1/2022 - 12/15/2022)
4. Regular: received month of renewal date between 16th-end of month (12/16/2022 - 12/31/2022)
5. Late: received month after renewal date (1/2023)
6. Too Late: received 2 months after renewal date (2/2023)

I've got the middle ones working, but am struggling with

• #1 (I can't figure out how to capture "if month of D4 is more than 1 month prior to D2, it's too early)
• #5 (because of the new year in this example, but it won't ALWAYS have a different year...)
• #6 (same thing as #5)

It's very possible I'm doing this the long way, but here's what I've got so far:
=IF(AND(MONTH(D2-1)>MONTH(D4),YEAR(D4)=YEAR(D2)),"EARLY","") this one works, but looks funny to me...
&IF(AND(MONTH(D4)=MONTH(D2),DAY(D4)<16,YEAR(D4)=YEAR(D2)),"TIMELY","")
&IF(AND(MONTH(D4)=MONTH(D2),DAY(D4)>=16,YEAR(D4)=YEAR(D2)),"REGULAR","")
&IF(AND(MONTH(D4)=MONTH(D2+1),YEAR(D4)=YEAR(D2)),"LATE","")
&IF(AND(MONTH(D4)>MONTH(D2+2),YEAR(D4)=YEAR(D2)),"TOO LATE. REAPPLY","") this one does not work

I've also got a couple other questions, such as

• Can I reformat a date to the end of the month if someone enters a random date? So, if I type "12/2/2022," the field will automatically update to "12/31/2022"?
• Can a drop-down list be coded to appear based on the result in another cell? So, if the renewal type is identified as "Late," a drop-down list will appear asking the user to select "Yes" or "No."

Please help!

 

[HongRu]

Try this.

=IF(D4<=EDATE(D2,-2),"Too Early","")&
IF(AND(D4>EDATE(D2,-2),D4<=EDATE(D2,-1)),"Early","")&
IF(AND(D4>EDATE(D2,-1),D4<=DATE(YEAR(D2),MONTH(D2),15)),"Timely","")&
IF(AND(D4>DATE(YEAR(D2),MONTH(D2),15),D4<=D2),"Regular","")&
IF(AND(D4>D2,D4<=EDATE(D2,1)),"Late","")&
IF(D4>EDATE(D2,1),"Too Late","")

Note that the code will show "Too Late" after the date "2/1/2023" while the renewal date is "12/31/2022".
I am not sure what you want is "2/1/2023" or "3/1/2023".
(You said "6.Too Late: received 2 months after renewal date (2/2023)". And it confuses me.)

 

[meganisagee]

Bless you, this worked! Thank you!

I'll keep an eye out for the other questions out of curiosity, but this was the most important part. Thanks again!!

 

[meganisagee]

Hello - I'm having issues with this calculator again! I entered 5/31/2023 for a 4/2023 renewal and it shows "TOO LATE, REAPPLY" when it should be a late renewal.

5/30/2023 seems to work. Please help.

Thanks!