Calculated field to show how much time has passed

Alex 116 Reputation points
2021-07-07T18:00:04.25+00:00

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?

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,737 questions
0 comments
Accepted answer
  1. Alex 116 Reputation points
    2021-07-08T21:53:23.747+00:00

    Thank you, @Elsie Lu_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

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Elsie Lu_MSFT 9,771 Reputation points
    2021-07-08T06:44:14.103+00:00

    Hi @Alex , 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.

    0 comments