# Date Generation formula #Invalid Value error, sometimes.

Options
✭✭✭✭

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!!

• ✭✭✭✭✭✭
Options

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)))

• ✭✭✭✭✭✭
Options

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)))

• ✭✭✭✭
Options

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!!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!