Automatic cell update

Hobo

I have a payment date column “A” (the specific day of each month specific payments are made) and another column “B” that denotes if the payment is fixed or variable payment. (fixed reoccurs on the same day each month)

For the payments identified as fixed  in column “B” i would like column “A” to automatically update to the next months payment date when the current payment date is in the past.

How can i achieve this is it a formula or an automated work? any help with this would be amazing

dojones

This is a complicated answer but maybe it will help point you in a direction.

Using the sheet below, yellow highlighted items are inputs, the remainder are calculations. Payment Date is the end result.

If a Fixed Payment, enter the date in Fixed Payment Date. If Variable, Enter Date in Variable Payment Date.

It may be possible to enter all in one field and use the Type Column to get to the Payment Due, but it's easier to explain this way.

These are formulas

Payment Date:

=IF(Type@row = "Fixed", [Fixed Payment Date Helper]@row, [Variable Payment Date]@row)

Variable Payment Date: No formula, input value

Fixed Payment Date: No formula, input value

Type:

=IF(ISBLANK([Variable Payment Date]@row), "Fixed", "Variable")

Fixed Payment Date Helper: Note that I hard coded 2024. I'll let you figure out how to get it from a formula.

=IF([Curr Date Helper]@row > [Fixed Payment Date]@row, [Next Payment Date]@row, [Fixed Payment Date]@row + "/2024")

CurrDate:

=TODAY()

Current Date Helper:

=LEFT(CurrDate@row, 2) + "/" + MID(CurrDate@row, 4, 2)

Year: Hard coded to 24. You can do a formula. Challenge is when year rolls over.

Fixed Month:

=LEFT([Fixed Payment Date]@row, 2)

Fixed Day:

=MID([Fixed Payment Date]@row, 4, 2)

Payment Date Month:

=VALUE(LEFT([Fixed Payment Date]@row, 2))

Next Payment Month:

=IF([Payment Date Month]@row + 1 = 13, 1, [Payment Date Month]@row + 1)

Next Payment Date:

=IF([Next Payment Month]@row < 10, "0" + [Next Payment Month]@row + "/" + [Fixed Day]@row + "/" + Year@row, [Next Payment Month]@row + "/" + [Fixed Day]@row + "/" + Year@row)

Best of luck.

Hobo

Thanks seems very complicated for what seems to be a simple recurring type function thanks i will give it a crack:-)

Hobo

@dojones Thanks it works great however i use day / month / year format and when i try to adjust the formulas i keep breaking them any chance you can help me with this?