Deducting months from date not returning correct year

Sharon Fitzsimons

Hi - Can anyone help please with deducting a number of months from a date and get it to return the correct year?

I have a contract expiration date column and a notice period (in months) column. I've used the below formula, but where I have a date of 31/03/2022 (Contract Expiration Date) and a notice period of 3 (Contract Notice Period), the result returned is 31/12/2019, instead of 31/12/2021.

If I change the Notice Period to 2, it correctly returns 31/01/2022.

=IFERROR(DATE(YEAR([Contract Expiration Date]@row), MONTH([Contract Expiration Date]@row) - [Contract Notice Period]@row, DAY([Contract Expiration Date]@row)), DATE(YEAR([Contract Expiration Date]@row) - [Contract Notice Period]@row, 12, DAY([Contract Expiration Date]@row)))

Can anyone tell me what I'm doing wrong please?

Thanks

Sharon

Toufong Vang

The formula below will work for what you're trying to do.

=IF(MONTH(DATE(YEAR([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), MONTH([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), DAY([Contract Expiration Date]@row))) = MONTH([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), DATE(YEAR([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), MONTH([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), DAY([Contract Expiration Date]@row)), DATE(YEAR([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), MONTH([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), DAY(DATE(YEAR([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), MONTH([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)) + 1, 1) - 1)))

Your formula is returning "31/12/2019" for "31/03/2022" because the expression MONTH([Contract Expiration Date]@row) - [Contract Notice Period]@row is resulting in an error. When the month is equal to or less than the notice period, the expression evaluates to 0 or less. In this case MONTH(31/3/2022) - 3 = 0; MONTH(0) errors out. As a result, your IFERROR() function returns the alternate value.

Equally important, the other part of your formula DAY([Contract Expiration Date]@row) will cause the wrong date to be returned when the contract expires on the 31st day of the month. February, April, June, September, and November do not have 31 days (although this will not cause an error.) For example, the expression DATE( 2021, 11, 31) will evaluate to 12/01/2021 when what you're after is 11/30/2021.

My formula assumes that there are 365/12 days in a month. It uses this value to help calculate the contract notice period. The second part of the IF() function calculates the last day of that month when expiry date falls on a day that is not in the notice period month (e.g., 31st). Otherwise, it uses the exact day

=IF( MONTH(DATE(YEAR([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), MONTH([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), DAY([Contract Expiration Date]@row))) = MONTH([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12))
	, DATE(YEAR([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), MONTH([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), DAY([Contract Expiration Date]@row))
	, DATE(YEAR([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), MONTH([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), DAY(DATE(YEAR([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), MONTH([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)) + 1, 1) - 1))
	)

Hope you found this helpful!

Paul Newcome

Will it always be three months, or is the number of months you need to subtract variable?

Toufong Vang

The formula below will work for what you're trying to do.

=IF(MONTH(DATE(YEAR([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), MONTH([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), DAY([Contract Expiration Date]@row))) = MONTH([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), DATE(YEAR([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), MONTH([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), DAY([Contract Expiration Date]@row)), DATE(YEAR([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), MONTH([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), DAY(DATE(YEAR([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), MONTH([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)) + 1, 1) - 1)))

Your formula is returning "31/12/2019" for "31/03/2022" because the expression MONTH([Contract Expiration Date]@row) - [Contract Notice Period]@row is resulting in an error. When the month is equal to or less than the notice period, the expression evaluates to 0 or less. In this case MONTH(31/3/2022) - 3 = 0; MONTH(0) errors out. As a result, your IFERROR() function returns the alternate value.

Equally important, the other part of your formula DAY([Contract Expiration Date]@row) will cause the wrong date to be returned when the contract expires on the 31st day of the month. February, April, June, September, and November do not have 31 days (although this will not cause an error.) For example, the expression DATE( 2021, 11, 31) will evaluate to 12/01/2021 when what you're after is 11/30/2021.

My formula assumes that there are 365/12 days in a month. It uses this value to help calculate the contract notice period. The second part of the IF() function calculates the last day of that month when expiry date falls on a day that is not in the notice period month (e.g., 31st). Otherwise, it uses the exact day

=IF( MONTH(DATE(YEAR([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), MONTH([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), DAY([Contract Expiration Date]@row))) = MONTH([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12))
	, DATE(YEAR([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), MONTH([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), DAY([Contract Expiration Date]@row))
	, DATE(YEAR([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), MONTH([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), DAY(DATE(YEAR([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)), MONTH([Contract Expiration Date]@row - ([Contract Notice Period]@row * 365 / 12)) + 1, 1) - 1))
	)

Hope you found this helpful!

Sharon Fitzsimons

@Paul Newcome The number of months will be variable - based on actual contract notice periods.

Sharon Fitzsimons

@Toufong Vang Thank you very much, and indeed yes, I had noticed some other minor issues with the dates, but this one was the real problem one. That works a treat.