Date Generation formula #Invalid Value error, sometimes.

Coen

Good morning, evening and afternoon all!

I have a formula that I am using to generate a Warranty End Date based off of an Order Date

=DATE(YEAR([Order Date | Warranty Start Date]@row) + 1, MONTH([Order Date | Warranty Start Date]@row) + 6, DAY([Order Date | Warranty Start Date]@row))

This seems to only work for me when the year is 2023. if the year is '19, '20, '21, or '22 I get the Invalid Value Error.

I'm not looking to suppress the error, but rather continue to calculate the Warranty End Date.

Any guidance is greatly appreciated!

tyty!!

Carson Penticuff

Your current formula is not accounting for months wrapping into the next year. If you have a start date with months 7-12, the result will be 13-18, which are invalid months. It looks like your warranty period is 18 months? This should work:

=IF(MONTH([Order Date | Warranty Start Date]@row) > 6, DATE(YEAR([Order Date | Warranty Start Date]@row) + 2, MONTH([Order Date | Warranty Start Date]@row) - 6, DAY([Order Date | Warranty Start Date]@row)), DATE(YEAR([Order Date | Warranty Start Date]@row) + 1, MONTH([Order Date | Warranty Start Date]@row) + 6, DAY([Order Date | Warranty Start Date]@row)))

Carson Penticuff

Your current formula is not accounting for months wrapping into the next year. If you have a start date with months 7-12, the result will be 13-18, which are invalid months. It looks like your warranty period is 18 months? This should work:

=IF(MONTH([Order Date | Warranty Start Date]@row) > 6, DATE(YEAR([Order Date | Warranty Start Date]@row) + 2, MONTH([Order Date | Warranty Start Date]@row) - 6, DAY([Order Date | Warranty Start Date]@row)), DATE(YEAR([Order Date | Warranty Start Date]@row) + 1, MONTH([Order Date | Warranty Start Date]@row) + 6, DAY([Order Date | Warranty Start Date]@row)))

Coen

Thank you, @Carson Penticuff !!

I pieced it together that it was the month and was coming back to update my post! Your suggestion worked like a charm!

Thank you!!