Calculate % complete with Date Range and Today's date

=IF([Observation Type]@row = \"Safety\", WORKDAY([Observation Date]@row, 2), IF([Observation Type]@row = \"Opportunity to Improve\", WORKDAY([Observation Date]@row, 14), IF([Observation Type]@row = \"Other Type\", WORKDAY([Observation Date]@row, 365), \"//m.santa-greenland.com/community/discussion/comment/\")))<\/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":[]},{"discussionID":107920,"type":"question","name":"How to remove text to only leave numbers","excerpt":"I have a column with numbers and letters i.e 1.1a, 1.1b, 2.1a and so on and using a simple formula to remove the letter and leave number =LEFT([SubComp#]@row, (LEN([SubComp#]@row) - 1)) which works just fine, but for some reason the Conditional Formatting does not recognize the calculated output as a number to highlight…","snippet":"I have a column with numbers and letters i.e 1.1a, 1.1b, 2.1a and so on and using a simple formula to remove the letter and leave number =LEFT([SubComp#]@row, (LEN([SubComp#]@row)…","categoryID":322,"dateInserted":"2023-07-21T15:21:47+00:00","dateUpdated":null,"dateLastComment":"2023-07-21T15:31:04+00:00","insertUserID":96691,"insertUser":{"userID":96691,"name":"gwson","url":"https:\/\/community.smartsheet.com\/profile\/gwson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bb_am_gUV_w!XFjMnwg6CTQ!MSWtq5sIJTM","dateLastActive":"2023-07-21T15:30:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":96691,"lastUser":{"userID":96691,"name":"gwson","url":"https:\/\/community.smartsheet.com\/profile\/gwson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bb_am_gUV_w!XFjMnwg6CTQ!MSWtq5sIJTM","dateLastActive":"2023-07-21T15:30:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":23,"score":null,"hot":3379907571,"url":"https:\/\/community.smartsheet.com\/discussion\/107920\/how-to-remove-text-to-only-leave-numbers","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107920\/how-to-remove-text-to-only-leave-numbers","format":"Rich","lastPost":{"discussionID":107920,"commentID":386451,"name":"Re: How to remove text to only leave numbers","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386451#Comment_386451","dateInserted":"2023-07-21T15:31:04+00:00","insertUserID":96691,"insertUser":{"userID":96691,"name":"gwson","url":"https:\/\/community.smartsheet.com\/profile\/gwson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bb_am_gUV_w!XFjMnwg6CTQ!MSWtq5sIJTM","dateLastActive":"2023-07-21T15:30:31+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-07-21T15:35:25+00:00","dateAnswered":"2023-07-21T15:30:02+00:00","acceptedAnswers":[{"commentID":386449,"body":"

Try wrapping it in a VALUE()<\/p>

=VALUE(LEFT([SubComp#]@row, (LEN([SubComp#]@row) - 1)))<\/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":[]},{"discussionID":107898,"type":"question","name":"Using MAX to get latest date by criteria","excerpt":"Hello, I am trying get the latest date in a list, subject to criteria but MAX COLLECT does not seem to work. What am I doing wrong? I have tried both a text column and a date column. =MAX(COLLECT({CQ Hours Data Range 6}, {CQ Hours Data Range 2}, Model@row, {CQ Hours Data Range 5}, Batch@row),) Appreciate any suggestions!","snippet":"Hello, I am trying get the latest date in a list, subject to criteria but MAX COLLECT does not seem to work. What am I doing wrong? I have tried both a text column and a date…","categoryID":322,"dateInserted":"2023-07-21T11:41:31+00:00","dateUpdated":null,"dateLastComment":"2023-07-21T12:14:07+00:00","insertUserID":135590,"insertUser":{"userID":135590,"name":"Jbob","url":"https:\/\/community.smartsheet.com\/profile\/Jbob","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxY995A_sfSQHsmgUsRgqeK4EHeVdf9h7QM2bqHd=s96-c","dateLastActive":"2023-07-21T12:55:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":135590,"lastUser":{"userID":135590,"name":"Jbob","url":"https:\/\/community.smartsheet.com\/profile\/Jbob","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxY995A_sfSQHsmgUsRgqeK4EHeVdf9h7QM2bqHd=s96-c","dateLastActive":"2023-07-21T12:55:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":35,"score":null,"hot":3379882538,"url":"https:\/\/community.smartsheet.com\/discussion\/107898\/using-max-to-get-latest-date-by-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107898\/using-max-to-get-latest-date-by-criteria","format":"Rich","lastPost":{"discussionID":107898,"commentID":386369,"name":"Re: Using MAX to get latest date by criteria","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386369#Comment_386369","dateInserted":"2023-07-21T12:14:07+00:00","insertUserID":135590,"insertUser":{"userID":135590,"name":"Jbob","url":"https:\/\/community.smartsheet.com\/profile\/Jbob","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxY995A_sfSQHsmgUsRgqeK4EHeVdf9h7QM2bqHd=s96-c","dateLastActive":"2023-07-21T12:55:50+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-07-21T12:14:33+00:00","dateAnswered":"2023-07-21T12:03:58+00:00","acceptedAnswers":[{"commentID":386362,"body":"

HI @Jbob<\/a> <\/p>

Thanks for the post. I have tried to recreate your example (in a simple way) to see where the issue is.<\/p>

In mine the column called Date and the column called Max per Value are BOTH Date data types.<\/p>

\n

\n \n <\/img><\/a>\n <\/div>\n<\/div>\n

I have exposed the formula I have used in the last column to show how the Max per Value is coming through.<\/p>

I know this has simplified your original question, but can you see how the Max(Collect()) statement works here?<\/p>

Does this help at all?<\/p>

So in yours: <\/p>

=MAX(COLLECT({CQ Hours Data Range 6}, {CQ Hours Data Range 2}, Model@row, {CQ Hours Data Range 5}, Batch@row),<\/strong>)<\/p>

Range 6 and the column you have written this formula into must be Date fields and you have an extra comma where you don't need it! Just between the last two )). This comma will cause an error.<\/p>

Kind regards<\/p>

Debbie<\/p>"},{"commentID":386369,"body":"

Hello Debbie,<\/p>

Appreciate your help, that worked perfectly!<\/p>

Thank you!<\/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

• How do I calculate different due dates on actions depending on the type of action?

  I have created a sheet in which different type of issues are identified and assigned to owners. I have established three "observation types" that I would like to assign different due date. For example, a safety issues would need a due date of 2 working days from the date identified, and an Opportunity to Improve action…

• How to remove text to only leave numbers

  I have a column with numbers and letters i.e 1.1a, 1.1b, 2.1a and so on and using a simple formula to remove the letter and leave number =LEFT([SubComp#]@row, (LEN([SubComp#]@row) - 1)) which works just fine, but for some reason the Conditional Formatting does not recognize the calculated output as a number to highlight…

• Using MAX to get latest date by criteria

  Hello, I am trying get the latest date in a list, subject to criteria but MAX COLLECT does not seem to work. What am I doing wrong? I have tried both a text column and a date column. =MAX(COLLECT({CQ Hours Data Range 6}, {CQ Hours Data Range 2}, Model@row, {CQ Hours Data Range 5}, Batch@row),) Appreciate any suggestions!