Aging and Status in Smartsheet
Above is a part of Service Request Database. May I know the formula for the following?
- Aging: The current formula I'm using is Today Column minus Start of Aging Column. May I know how I can stop aging those with Requests that are resolved already (with dates stated in the Date Resolved Column)? Or is there any way I can age this using a shorter formula/process?
- Status: Is it possible that Status if filled out depending on the value from other cells? Like the status will be "On Going" if there is a value in Date of Inspection and/or date of service. The status will change to completed if there is value in the date resolved column. The status will be pending if all 3 columns (date of inspection, date of service and date of resolved" are blanks.
Thanks in advance for your answer.
Answers
-
Hi Mariniel,
For you aging part:
=IF(ISDATE([DATE RESOLVED]), "", [Today Column]@row - [START OF AGING]@row)
What this will do is, if it founds the date in "Date Resolved" column then it won't do anything, else it will do your aging calculation.
For your status part, you can either create a new column for this or apply the formula in same status column:
=IF(OR(ISDATE([DATE OF INSPECTION]@row), ISDATE([DATE OF SERVICE]@row)), "On Going", IF(ISDATE([DATE RESOLVED]@row), "Completed", IF(AND(ISDATE([DATE RESOLVED]@row) = false, ISDATE([DATE OF SERVICE]@row) = false, ISDATE([DATE OF INSPECTION]@row) = false), "Pending", "")))
Hope this helps!
Thanks,
Jayesh
-
Hi Jayesh. For the changing of status, I tried your formula but for the "Completed" status, it appears as "On Going" also. My condition for the status "Completed" is if the Date of Inspection, Date of Service and Date Resolved have dates.
Another question, for the aging part, is it possible to not have the start of aging date unless the date comes already? Because my ticket starts to age after 7 days from the day of inspection. Currently, if I already have the date where it starts to age, the total in the Aging column gives a negative value since start of aging is greater than the today date.
-
I find that if you move the IF statement for the "DATE RESOLVED" = Completed to the front of the statement would solve your problem.
e.g.
=IF(ISDATE([DATE RESOLVED]@row), "Completed", IF(OR(ISDATE([DATE OF INSPECTION]@row), ISDATE([DATE OF SERVICE]@row)), "On Going", IF(AND(ISDATE([DATE RESOLVED]@row) = false, ISDATE([DATE OF SERVICE]@row) = false, ISDATE([DATE OF INSPECTION]@row) = false), "Pending", "")))
-
@Jayesh Nathani I am trying to use your aging formula but getting Unparseable. Can you look at my specific formula and help me understand what's wrong?
I am trying to say that if the row (4 in this case) has an Approval Status of "Under Review" then calculate the aging of "Date Submitted" - "Last Modified Date" but if it's in "Approved, Declined, or Processed" then do nothing.
=IF(APPROVAL STATUS,UNDER REVIEW,[APPROVED, DECLINED, PROCESSED]), "",[DATE SUBMITTED]4 - [LAST MODIFIED DATE]4)
Thank you!
@Paul Newcome and as always, tagging you, my friend, because you are my SS guru! LOL
-
Ooh, nevermind! I think I found my solution in a different thread. Thanks!
-
@Kelly Gabel You said you "think" you found your solution, but just in case you didn't...
=IF([Approval Status]@row = "Under Review", [Date Submitted]@row - [Last Modified Date]@row)
The above is assuming that the only possible options in the [Approval Status] column are the 4 you have listed.
-
Thanks Paul! That's what I used! Appreciate your help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
