Date Generation formula #Invalid Value error, sometimes.

Good morning, evening and afternoon all!

I have a formula that I am using to generate aWarranty End Datebased off of anOrder 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:

    =如果(月([|保修开始日期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:

    =如果(月([|保修开始日期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 theFormula Handbook template!
HI @SailingSheets<\/a> <\/p>

This formula should do the trick for you<\/p>

=IF(AND([Target Milestone Date]@row - TODAY() > 14, Progress@row = \"Not Started\"), \"On Track\", IF(AND([Target Milestone Date]@row - TODAY() > 7, Progress@row = \"In Progress\"), \"On Track\", IF(AND([Target Milestone Date]@row - TODAY() < 14, [Target Milestone Date]@row - TODAY() > 6, Progress@row = \"Not Started\"), \"At Risk\", IF(AND([Target Milestone Date]@row - TODAY() < 7, Progress@row = \"In Progress\"), \"At Risk\", IF(AND([Target Milestone Date]@row - TODAY() < 7, Progress@row = \"Not Started\"), \"Behind\", IF(AND([Target Milestone Date]@row <= TODAY(), ISBLANK(Progress@row)), \"Behind\", IF(Progress@row = \"Complete\", \"//www.santa-greenland.com/community/discussion/107725/\", \"Error\")))))))<\/p>

Tested and working as a column formula<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope that helps<\/p>

Thanks<\/p>

Paul<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":319,"urlcode":"functionality","name":"functionality"},{"tagID":334,"urlcode":"automations","name":"Automations"}]},{"discussionID":109315,"type":"question","name":"Countifs statements for date fields that are in the past","excerpt":"Hi there, I am looking to summarize data from a master sheet for inclusion into a dashboard via a project metrics sheet. Within the master sheet I have an “Opening Date” that is populated when the opening date is decided. I want to populate the Dashboard with the number of sites that HAVE opened each month (past tense).…","snippet":"Hi there, I am looking to summarize data from a master sheet for inclusion into a dashboard via a project metrics sheet. Within the master sheet I have an “Opening Date” that is…","categoryID":322,"dateInserted":"2023-08-22T23:42:50+00:00","dateUpdated":null,"dateLastComment":"2023-08-23T19:17:51+00:00","insertUserID":164110,"insertUser":{"userID":164110,"name":"Derrick P","title":"Sr Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Derrick%20P","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-23T19:17:24+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164110,"lastUser":{"userID":164110,"name":"Derrick P","title":"Sr Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Derrick%20P","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-23T19:17:24+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":42,"score":null,"hot":3385568441,"url":"https:\/\/community.smartsheet.com\/discussion\/109315\/countifs-statements-for-date-fields-that-are-in-the-past","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109315\/countifs-statements-for-date-fields-that-are-in-the-past","format":"Rich","tagIDs":[218,254,440],"lastPost":{"discussionID":109315,"commentID":392204,"name":"Re: Countifs statements for date fields that are in the past","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392204#Comment_392204","dateInserted":"2023-08-23T19:17:51+00:00","insertUserID":164110,"insertUser":{"userID":164110,"name":"Derrick P","title":"Sr Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Derrick%20P","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-23T19:17:24+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-23T19:17:21+00:00","dateAnswered":"2023-08-23T18:43:33+00:00","acceptedAnswers":[{"commentID":392183,"body":"

You don't need any helper columns. All you need to do is add a range\/criteria set to your existing monthly COUNTIFS to include the date range being less than today.<\/p>

=COUNTIFS({Opening Date}, IFERROR(MONTH(@cell), 0) = 8, {Opening Date}, IFERROR(YEAR(@cell), 0) = 2023, {Opening Date}, @cell<= TODAY()<\/strong>)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":218,"urlcode":"construction","name":"Construction"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}]},{"discussionID":109322,"type":"question","name":"Calculate age from a birthday date","excerpt":"Hello, We receive the birthday date from a form and I want to put on cell the age automatically. How can we do to make a formula to put the age form a birthday date. I think this works. =(TODAY()-(birthday))365 Thanks for your help Johann","snippet":"Hello, We receive the birthday date from a form and I want to put on cell the age automatically. How can we do to make a formula to put the age form a birthday date. I think this…","categoryID":322,"dateInserted":"2023-08-23T06:27:09+00:00","dateUpdated":"2023-08-23T08:18:54+00:00","dateLastComment":"2023-08-23T11:41:01+00:00","insertUserID":163782,"insertUser":{"userID":163782,"name":" johann","title":"Sr.","url":"https:\/\/community.smartsheet.com\/profile\/%20johann","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!4ScquMIXUDo!pKjc6UoqrBc!67g8iLwk1n4","dateLastActive":"2023-08-23T19:07:05+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":163782,"lastUserID":163782,"lastUser":{"userID":163782,"name":" johann","title":"Sr.","url":"https:\/\/community.smartsheet.com\/profile\/%20johann","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!4ScquMIXUDo!pKjc6UoqrBc!67g8iLwk1n4","dateLastActive":"2023-08-23T19:07:05+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":35,"score":null,"hot":3385564090,"url":"https:\/\/community.smartsheet.com\/discussion\/109322\/calculate-age-from-a-birthday-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109322\/calculate-age-from-a-birthday-date","format":"Rich","lastPost":{"discussionID":109322,"commentID":392073,"name":"Re: Calculate age from a birthday date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392073#Comment_392073","dateInserted":"2023-08-23T11:41:01+00:00","insertUserID":163782,"insertUser":{"userID":163782,"name":" johann","title":"Sr.","url":"https:\/\/community.smartsheet.com\/profile\/%20johann","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!4ScquMIXUDo!pKjc6UoqrBc!67g8iLwk1n4","dateLastActive":"2023-08-23T19:07:05+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-23T11:42:05+00:00","dateAnswered":"2023-08-23T08:39:00+00:00","acceptedAnswers":[{"commentID":392053,"body":"

Hi johann,<\/p>

if you just add the \"\/\" I think it works.<\/p>

=(TODAY() - ([birth date]@row)) \/ 365<\/p>

\n \n 2023-08-23 10_37_31-birthday date calc - Smartsheet.com.png\n <\/a>\n<\/div>\n

is this what you are expecting ?<\/p>

best regards<\/p>

Florian<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions