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
What I really want is a way for the Due Date column to list the current date with the day the bills due and not update until it is the following month and column F4 is equal to "Cancelled", "Paid", "Paid Off", OR "Paused".
FOR EXAMPLE the due date for cell E4 = (10/01/2023) and it will not update to (11/01/2023) until the current date is November 2023 AND cell F4 = "Cancelled", "Paid", "Paid Off", OR "Paused".
Any help would be appreciated.
Thanks.
