Auto update due dates if conditions are met

**twint022** · 10-05-2023, 11:16 AM

I am trying to write a formula for a Due Date that will automatically update to the new Month/Day/Year, but only if the value in cell F4 equals ANY of the following: "Cancelled", "Paid", "Paid Off", OR "Paused"

Currently In cell A1 I have today's day as A1 =TODAY()

I am getting the Date in cell E4 by using the following formula: =IF(AND(DAY($A$1) = 1, OR(F4 = "Cancelled", F4 = "Paid", F4 = "Paid Off", F4 = "Paused")), DATE(YEAR($A$1), MONTH($A$1), 1), DATE(YEAR($A$1), MONTH($A$1), 1))
---The formula above checks IF two conditons are BOTH TRUE: 1) IF the day of the month in cell A1 is the first of the month AND 2) IF the value in cell F4 equals ANY of the following: "Cancelled", "Paid", "Paid Off", OR "Paused".
---IF BOTH CONDITIONS ARE TRUE, then cell E4 will extract the Year and Month from A1 and enter the day as 1.

---IF ONE OR BOTH ARE FALSE it will extract the Year and Month from cell A1 and enter the day as 1. --> This is my issue. I am getting my dates by extracting from Today's date in cell A1. [A1 =TODAY()]. Doing this means that even if BOTH conditons are not met the date is still updating to the next month.

Bills.png

Any help on a better way to do this is appreciated.

Thanks.

**AliGW** · 10-05-2023, 11:24 AM

Welcome to the forum.

Which version of Excel do you have? Is it 365? Is it for Mac?

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.