question

Alex-6587 avatar image
0 Votes"
Alex-6587 asked ElsieLu-MSFT commented

Calculated field to show how much time has passed

Hello. I'm new to SharePoint and did some searching on this forum and elsewhere but couldn't find a solution. Maybe I don't know enough to narrow my search :)


I have a Choice type column called "Loan Stage". The choices are:
Stage 1 - LOI
Stage 2 - Underwriting/Loan Processing
Stage 3 - Loan Documentation
Stage 4 - Boarding
Stage 5 - Closing
Stage 6 - Post Closing
Stage 7 - Closed

Our goal is to create a new View that shows time (in days) between Stage 1 - LOI and Stage 7 - Closed. The new view will be called "Days in Stage".

I think I need to create some new columns to get the date that the loan was in Stage 1 - LOI and when it was Stage 7 - Closed, then another calculated field to get the time (in days). Am I on the right track?

office-sharepoint-online
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Alex-6587 avatar image
0 Votes"
Alex-6587 answered ElsieLu-MSFT commented

Thank you, @ElsieLu-MSFT! I'd like for the calculated column to show the number of days elapsed between "Stage 1 - LOI" and "Stage 7 - Closed" - is that possible?

So if the loan were opened today and it was closed tomorrow, then the calculated column should have a value of '1'
and if the loan were opened today and it was closed 2 days from now, then the calculated column should have a value of '2'
and so on

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Alex-6587 , I have updated my answer, please kindly check the Update1 section. Feel free to reply if you have any doubt. :)

0 Votes 0 ·

Hi @Alex-6587 ,

Does this solution meet your needs? Is my suggestion helpful?
I am looking forward to your reply. Please don't hesitate to let us know if there are any questions.

Thanks,
Elsie Lu

0 Votes 0 ·

Hi @ElsieLu-MSFT - Yes, I've been playing around with this and it looks like it will work for us. Thank you for all your help.

0 Votes 0 ·

Hi @Alex-6587 ,

Glad to hear that my advice is helpful. For a better user experience, please accept my solution as the answer, which will allow users with the same needs in the future to directly see the most useful content.

And any updates about this thread can be posted here, and we will continue to follow up.

Thank you for your understanding and support. :)

Have a nice day!



0 Votes 0 ·
ElsieLu-MSFT avatar image
0 Votes"
ElsieLu-MSFT answered ElsieLu-MSFT edited

Hi @Alex-6587 , welcome to Q&A forum!

Yes, you are right, this requirement is achievable. :)

Please follow these steps:

1.Create a new view named "Days in Stage" and filter the items you want. I would suggest you use is equal to stage 1....OR is equal to stage2...OR ...... Create 7 is equal to to filter items:
112894-33.jpg

2.Create a Calculated column and use this formula:

 =IF(OR([Loan Stage]="Stage 1 - LOI",[Loan Stage]="Stage 7 - Closed"),Created,"N/A")

=========================Update1=====================
Let me explain:

What Calculated column can get is the time when this item was created: [Created], the time when it was modified [Modified], or the time in a new column. Using [Today] in a calculated column is pointless because the value get updated when the item is updated. In that way is it the same as the modified column.

I would suggest you create a Date and Time, For example we named it "CLOSED", then use this formula to calculate the number of days between the creation of the item and the closing time you specify:

 =DATEDIF(Created,CLOSED,"d")

113158-35.jpg


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


6.gif (179.6 KiB)
33.jpg (67.9 KiB)
35.jpg (42.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.