Date Generation formula #Invalid Value error, sometimes.

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

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

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

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!