Formula to increase the year value in a date field

Options

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)), "")

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!