Aging and Status in Smartsheet

Mariniel Bautista

Above is a part of Service Request Database. May I know the formula for the following?

1. 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?
2. 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.

Jayesh Nathani

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

Mariniel Bautista

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.

Joo Tat Teoh

https://community.smartsheet.com/discussion/comment/239270#Comment_239270

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

Kelly Gabel

@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

Kelly Gabel

Ooh, nevermind! I think I found my solution in a different thread. Thanks!

Paul Newcome

@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.

Kelly Gabel

Thanks Paul! That's what I used! Appreciate your help!