I need help with "IF" function and conditional formatting

To apply the conditional formatting to the whole column, you can select the entire column E by clicking on the column header. Then, go to the "Conditional Formatting" menu, select "New Rule," and enter the formula =TODAY()<=E1 into the formula field. The E1 is used instead of E2 because the conditional formatting formula is based on the top cell in the selected range.

Regarding the code assignment, the formula I provided earlier should work correctly based on the criteria you mentioned. Here it is again for reference:

=IF(E2=C2+28*365,"Code 1",IF(E2=D2+11*365,"Code 2",""))

Hi Kristi

I'm AnnaThomas and I'd be happy to help you with your question. In this Forum, we are Microsoft consumers just like yourself.

Please follow the steps below

Step 1: Calculating the Expiry Date

To calculate the expiry date in column E, you can use the following IF statement:

=IF(DATEDIF(C2,D2,"y")<18,C2+28365,D2+11365)

Step 2: Assigning Expiry Code

To assign an expiry code in column F based on the expiry date in column E, you can use the following IF statement:

=IF(E2=C2+28*365,"Code 1","Code 2")

Step 3: Conditional Formatting

To highlight the cell in column E if the date is today or later, you can use conditional formatting:

Select the cells in column E that you want to apply the formatting to. Click on "Conditional Formatting" in the "Home" tab. Select "New Rule" from the drop-down menu. Select "Use a formula to determine which cells to format". In the formula field, enter the following formula:

=TODAY()<=E2

Click on the "Format" button and select the red fill color. Click "OK" to apply the formatting.

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

This worked beautifully.

But I was wrong about the code assignment and highlighting working.

The highlighting will work if I do each cell individually but how do I apply this rule to the whole column?

Any I'm not sure where we are going wrong with the code assignment... to recap, I want If E is 28 years after C than it's "code 1" but if E is 11 years after D than it's "code 2"

To calculate the number of days between two dates while accounting for leap years, we can use the DATEDIF function with the "D" parameter.

Here is the updated formula for calculating the expiry date:

=IF(DATEDIF(C2,D2,"y")<18,DATE(YEAR(C2)+28,MONTH(C2),DAY(C2)),DATE(YEAR(D2)+11,MONTH(D2),DAY(D2)))

So assigning the code worked perfectly, as did the highlighting, but the expiry date calculation is wildly off.

Example. The date of birth is August 20 1993 and the appointment date is October 2 1998. The expiration date should be August 20 2021, but the formula is making the expiration date April 18 2071....