Trying to create a new date based on an existing date minus 9 months, 12 months, 18 months, etc.

mike.thorpe17421

I have an existing date column [First Commercial Sales Date (Best Case)] and want a few separate columns to show new dates as follows: 

- 9 months

- 12 months

- 18 months

- 24 Months and 

+ 3 months

i.e. 

The [First Commercial Sales Date (Best Case)] date is 22/11/2025 (22 November 2025) and the new date (minus 9 months) should be 22/02/2025 (22 February 2025)

The [First Commercial Sales Date (Best Case)] date is 22/11/2025 (22 November 2025) and the new date (minus 12 months) should be 22/11/2024 (22 November 2025)

Andrée Starå provided me with the following formula for 12 months but I cannot seem to work out the formula where the period is 3 months, 9 months, 18 months etc.

=
DATE(YEAR([First Commercial Sales Date (Best Case)]@row) - 1, 
MONTH([First Commercial Sales Date (Best Case)]@row), 
DAY([First Commercial Sales Date (Best Case)]@row))

Can anybody help?

Paul Newcome

Try these:

- 9 months:

=IFERROR(DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) - 9, DAY([Date Column]@row)), DATE(YEAR([Date Column]@row) - 1, MONTH(Date Column]@row) + 3, DAY([Date Column]@row)))

- 12 months:

=DATE(YEAR([Date Column]@row) - 1, MONTH([Date Column]@row), DAY([Date Column]@row))

- 18 months:

=IFERROR(DATE(YEAR([Date Column]@row) - 1, MONTH([Date Column]@row) - 6, DAY([Date Column]@row)), DATE(YEAR([Date Column]@row) - 2, MONTH(Date Column]@row) + 6, DAY([Date Column]@row)))

- 24 Months:

=DATE(YEAR([Date Column]@row) - 2, MONTH([Date Column]@row), DAY([Date Column]@row))

+ 3 months:

=IFERROR(DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) + 3, DAY([Date Column]@row)), DATE(YEAR([Date Column]@row) + 1, MONTH(Date Column]@row) - 9, DAY([Date Column]@row)))

If you wanted to use a column to enter a variable number of dates and use a single formula to calculate:

=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)), 1), 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)), 1))