Add month in a date

Michael.S

Hello,I searched the forum but couldn't find any formula that worked.

I'm trying to add a value (value defined in a column) to a date.

I try the formula : =DATE(DAY([Contrôle effectué en]@row:[Prochain contrôle]@row); MONTH([Contrôle effectué en]@row:[Prochain contrôle]@row) + [Fréquence d'entretien]@row; YEAR(([Contrôle effectué en]@row:[Prochain contrôle]@row)))

But it doesn't work.

Can you help me ?


    

        
            
    

Paul Newcome

Here is a formula that allows for any number of months both positive and negative. You will jest need to adjust the two different column names to match what you have in your sheet.

=IFERROR(IFERROR(DATE(YEAR([Original Date]@row) + ROUNDDOWN((MONTH([Original Date]@row) + [Number of Months]@row) / 12, 0) + IF(IF(MOD(MONTH([Original Date]@row) + [Number of Months]@row, 12) = 0, 12, MOD(MONTH([Original Date]@row) + [Number of Months]@row, 12)) = 12, -1) - IF(AND(ABS([Number of Months]@row) - MONTH([Original Date]@row) <> 12, [Number of Months]@row < 0, ABS([Number of Months]@row) > MONTH([Original Date]@row)), 1, 0), IF(MOD(MONTH([Original Date]@row) + [Number of Months]@row, 12) = 0, 12, MOD(MONTH([Original Date]@row) + [Number of Months]@row, 12)), DAY([Original Date]@row)), DATE(IF(MONTH([Original Date]@row) - ABS([Number of Months]@row) < 1, YEAR([Original Date]@row) - 1, YEAR([Original Date]@row)), IF(MONTH([Original Date]@row) - ABS([Number of Months]@row) < 1, MONTH([Original Date]@row) + (12 - ABS([Number of Months]@row)), MONTH([Original Date]@row) - ABS([Number of Months]@row)), DAY([Original Date]@row))), "")

MarceHolzhauzen

Hi @Michael S.

The challenge you're having is that you are referencing an array instead of a single column. Smartsheet does not support referencing multiple columns in a single DATE function like this

You need to use only one date field as input (either [Contrôle effectué en]@row or [Prochain contrôle]@row),

The formula will end up looking something like this:

=DATE(YEAR([Contrôle effectué en]@row), MONTH([Contrôle effectué en]@row) + [Fréquence d'entretien]@row, DAY([Contrôle effectué en]@row))

Explanation:

1. YEAR([Contrôle effectué en]@row):
   • Extracts the year from the [Contrôle effectué en]@row date.
2. MONTH([Contrôle effectué en]@row) + [Fréquence d'entretien]@row:
   • Adds the value from [Fréquence d'entretien]@row (assumed to be in months) to the month of the [Contrôle effectué en]@row date.
3. DAY([Contrôle effectué en]@row):
   • Extracts the day from the [Contrôle effectué en]@row date to maintain the same day in the resulting date.

Michael.S

Hi Marce,

Thanks for your response.

I try your formula, but it's not working :

Edit : In the end, this works with a number below 12, how can we ensure that the date can move to the next year in the case of adding 12?

Paul Newcome

Here is a formula that allows for any number of months both positive and negative. You will jest need to adjust the two different column names to match what you have in your sheet.

=IFERROR(IFERROR(DATE(YEAR([Original Date]@row) + ROUNDDOWN((MONTH([Original Date]@row) + [Number of Months]@row) / 12, 0) + IF(IF(MOD(MONTH([Original Date]@row) + [Number of Months]@row, 12) = 0, 12, MOD(MONTH([Original Date]@row) + [Number of Months]@row, 12)) = 12, -1) - IF(AND(ABS([Number of Months]@row) - MONTH([Original Date]@row) <> 12, [Number of Months]@row < 0, ABS([Number of Months]@row) > MONTH([Original Date]@row)), 1, 0), IF(MOD(MONTH([Original Date]@row) + [Number of Months]@row, 12) = 0, 12, MOD(MONTH([Original Date]@row) + [Number of Months]@row, 12)), DAY([Original Date]@row)), DATE(IF(MONTH([Original Date]@row) - ABS([Number of Months]@row) < 1, YEAR([Original Date]@row) - 1, YEAR([Original Date]@row)), IF(MONTH([Original Date]@row) - ABS([Number of Months]@row) < 1, MONTH([Original Date]@row) + (12 - ABS([Number of Months]@row)), MONTH([Original Date]@row) - ABS([Number of Months]@row)), DAY([Original Date]@row))), "")

Michael.S

It's working :D Thank you.

Paul Newcome

Happy to help. 👍️