Help with formula please! Add months to date

FionaAU

Greeting from Australia,

I have a formula (below) to calculate a when the next service is due. The service cycle column is form a drop down selection of 3, 6 or 12 months. I would like when the service cycle is selected, and the service/condemned date is updated that it automatically populates the "next PM Due" column.

The below formula is working for 3 and 6, months, but not 12 and I cant work out why? Here is my data

=DATE(YEAR([Serviced/Condemned Date]@row) + INT((MONTH([Serviced/Condemned Date]@row) + [Service Cycle]@row) / 12), MOD(MONTH([Serviced/Condemned Date]@row) + [Service Cycle]@row, 12), DAY([Serviced/Condemned Date]@row))

Thank you for helping me.

Cheers Fiona

che.rabajante

Hello @FionaAU

Please try this:

Adjust the formula to handle this case by ensuring that month 0 becomes month 12 and adjusting the year accordingly.

=DATE(YEAR([Serviced/Condemned Date]@row) + INT((MONTH([Serviced/Condemned Date]@row) + [Service Cycle]@row - 1) / 12), MOD(MONTH([Serviced/Condemned Date]@row) + [Service Cycle]@row - 1, 12) + 1, DAY([Serviced/Condemned Date]@row)).

Hope this helps.

Che

che.rabajante

Hello @FionaAU

Please try this:

Adjust the formula to handle this case by ensuring that month 0 becomes month 12 and adjusting the year accordingly.

=DATE(YEAR([Serviced/Condemned Date]@row) + INT((MONTH([Serviced/Condemned Date]@row) + [Service Cycle]@row - 1) / 12), MOD(MONTH([Serviced/Condemned Date]@row) + [Service Cycle]@row - 1, 12) + 1, DAY([Serviced/Condemned Date]@row)).

Hope this helps.

Che

FionaAU

Thank you Che - That is absolutely wonderful, your reply was so quick and fixed the problem.

So very kind of you for helping.

Thank you again.

🤩