Formula Help
I am trying to fix a formula. What I am trying to achieve is the number of days between the Assigned Date and the Created Date. The "Executed" is for when the status changes to Executed. I have a status column and date columns following for the statuses like "Assigned" , "Pending" etc. and for each status is a date column that is auto populated.
=IF([Created] = "", TODAY() - [Assigned Date]@row, "Executed") -this is coming up #unparseable
(I don't quite understand the formula above, but it was left by another staff person and I need to fix it)
If I can get it to work right, it would have a number in that column until it becomes "Executed" I hope it all makes sense.
TIA
Ali
Best Answer
-
Paul Newcome ✭✭✭✭✭✭Give this a try:
=TODAY() - IF([Assigned Date]@row <> "", [Assigned Date]@row, DATEONLY(Created@row))
Answers
-
Paul Newcome ✭✭✭✭✭✭
-
AliT ✭✭
That did work and it's no longer #unparseable but now every cell shows "Executed" Is there something else I need to add or remove to get numbers to appear?
-
Paul Newcome ✭✭✭✭✭✭
What type of column is Created? If it is a system generated Created (Date) type column, it will never be blank which is what you have as your logical statement in the IF function.
=IF(Created@row = "", TODAY() - [Assigned Date]@row, "Executed")
Your formula is basically saying to calculate the days if the Created column is blank.
-
AliT ✭✭
Sheesh. Yes, the created column is the system generated one. UMPH. I didn't build this formula and I am fairly new at this. We want a formula that looks at the created date and the assigned date and give me a number of days that it is taking to complete the item, so it also needs to look at today's date unless it's been executed then display that instead of the number. The column the number is displayed is Days to Process.
-
Paul Newcome ✭✭✭✭✭✭
Give this a try:
=TODAY() - IF([Assigned Date]@row <> "", [Assigned Date]@row, DATEONLY(Created@row))
-
AliT ✭✭
You are a wonderful person!! That worked!! Thank you so much!!
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
I believe you need to put brackets around your column references, so the updated formula would be:<\/p>
=SUMIFS({Quantity}, {Product}, [Product]$1, {Month}, [Month]$1, {Vendor}, [Vendor]$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":109252,"type":"question","name":"RAID Log Date Value Locking per column when status changes","excerpt":"Hello everyone, I have tried to find the best answer for this but I see too many threads and different responses. If anyone could help as I am new to this. I am only looking to lock in a date value on 2 columns whenever there is a status change, from when an issue is raised and then resolved. I know there is a record a…","snippet":"Hello everyone, I have tried to find the best answer for this but I see too many threads and different responses. If anyone could help as I am new to this. I am only looking to…","categoryID":322,"dateInserted":"2023-08-22T07:34:24+00:00","dateUpdated":null,"dateLastComment":"2023-08-22T11:49:33+00:00","insertUserID":165418,"insertUser":{"userID":165418,"name":"Jsantos","title":"Technical services","url":"https:\/\/community.smartsheet.com\/profile\/Jsantos","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T10:15:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T15:25:08+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3385396437,"url":"https:\/\/community.smartsheet.com\/discussion\/109252\/raid-log-date-value-locking-per-column-when-status-changes","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109252\/raid-log-date-value-locking-per-column-when-status-changes","format":"Rich","tagIDs":[319,437,448],"lastPost":{"discussionID":109252,"commentID":391803,"name":"Re: RAID Log Date Value Locking per column when status changes","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391803#Comment_391803","dateInserted":"2023-08-22T11:49:33+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T15:25:08+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-22T12:39:56+00:00","dateAnswered":"2023-08-22T08:42:10+00:00","acceptedAnswers":[{"commentID":391778,"body":"
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/F9FEGECSTNO0\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/VURVHNTYI02I\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/3RZ68WZXTV10\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/IH0AJ4O62S1C\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
