Formula to increase the year value in a date field

EddieChi

I created a billing form that's going to be used to capture future billings to clients. The form is set up to capture the 1st billing date & # of future monthly installments, and the sheet has the formula that populates the billing schedule (ie 2/15/22, 3/15/22, 4/15/22,...).

The error pops up when the formula has to return a value for 2023. Since the formula just adds 1 to the initial billing month, it returns 13/01/2022, instead of 01/01/2023 for something that needs to be billed in Jan 2023.

Is there a tweak I can make to the date formula to change it to the next year after Dec 2022?

This is the formula:

=IF([Recurring Billing Periods]@row > 2, DATE(YEAR([Date1]@row), MONTH([Date1]@row) + 2, DAY([Date1]@row)), "")

Genevieve P.

Hi @EddieChi

You can add an IF statement in to see what the month is, and if it's either 11 or 12 to add to the year and subtract a specific number of months instead.

Ex:

IF(MONTH([Date1]@row) = 11, DATE(YEAR([Date1]@row) + 1, MONTH([Date1]@row) - 10, DAY([Date1]@row))

Try this:

=IF([Recurring Billing Periods]@row > 2, IF(MONTH([Date1]@row) = 11, DATE(YEAR([Date1]@row) + 1, MONTH([Date1]@row) - 10, DAY([Date1]@row)), IF(MONTH([Date1]@row) = 12, DATE(YEAR([Date1]@row) + 1, MONTH([Date1]@row) - 10, DAY([Date1]@row)), DATE(YEAR([Date1]@row), MONTH([Date1]@row) + 2, DAY([Date1]@row)))), "")

Cheers,

Genevieve

Genevieve P.

Hi @EddieChi

You can add an IF statement in to see what the month is, and if it's either 11 or 12 to add to the year and subtract a specific number of months instead.

Ex:

IF(MONTH([Date1]@row) = 11, DATE(YEAR([Date1]@row) + 1, MONTH([Date1]@row) - 10, DAY([Date1]@row))

Try this:

=IF([Recurring Billing Periods]@row > 2, IF(MONTH([Date1]@row) = 11, DATE(YEAR([Date1]@row) + 1, MONTH([Date1]@row) - 10, DAY([Date1]@row)), IF(MONTH([Date1]@row) = 12, DATE(YEAR([Date1]@row) + 1, MONTH([Date1]@row) - 10, DAY([Date1]@row)), DATE(YEAR([Date1]@row), MONTH([Date1]@row) + 2, DAY([Date1]@row)))), "")

Cheers,

Genevieve