Microsoft Teams, Lists, Calculated Columns

Question

Hey everyone, I'm trying to figure out how to set up a column that displays the time difference between the list creation, and when I choose that a task is completed. I have tried a few different things with no luck so far. The current formula I'm trying to use is this: =IF ([Status]=Completed) THEN ([Now()-Created]*24). I keep getting the Syntax errors when trying to create the column. On my Status column I have 3 options Completed being the one I want to use as my completed time generator in this Total Down-time column I'm trying to create. If anyone has any in-sight into what I'm doing wrong or missing please let me know. I attached an image of SharePoint because in teams Lists you cannot get to the screen where you can create a calculated column.

Thanks,

-Andrew

 [PII is masked by Gloria Li MSFT Support]

Answer 1

This is what I tried last, I am still getting the Syntax error. Please let me know if you need more information on what I'm trying to do.

Thanks!

-Andrew

Answer 2

Okay Gloria, I think I figured out my issue with my formula. I typed it up as if I had a Completed column, however what I was trying to attain was on my Issue/Item on the list I select Completed on my Status column then I want to calculate the time difference from when the item was created until the current time when I chose that it was completed on my Status column. I'd like to just use the Created column automatically generated on the list for my start time, and use the current time for my Completion time. I'd like it to display as the difference in time between the creation and completion. Do I need to create a column for created time instead of using the default to make this work? I was trying to create a column for created time however I didn't have much luck, I think I need to use the =Now() in my formula box and it will pull up the current time into the box on the column but I'm not 100% certain on that.

If I want to set this up using Completed on my selection from my Status Column. How would I make my formula? =IF([Status]=Completed), (TODAY()-[Created])*24, would this work just removing the [brackets] from the Completed? I'm going to keep trying to figure it out, but if you figure it out before me I would love to see what you find.

Also, is TODAY() the appropriate syntax to use for time? From my research I found that NOW() uses the current date & time where as TODAY() is only the current date. I could be wrong but I have read this from many different sources.

Thank you again! I appreciate the help so far!

-Andrew

Answer 3

Hi Andrew,

Thanks for cooperation and patience, given you situation, we are trying to test it on our side:

1. Create two Single line of text as “Status” and “Completed”, and also a Date and time column as “CreatedTime”,.
2. Create Calculated column as below:

May I know this is what you mean? If not, could you please point out and tell us more?

Best regards,

Gloria

Answer 4

Thank you Gloria, I will patiently wait for your reply.

Answer 5

Hi Andrew,

Thanks for sharing your experience in our community!

About your issue, we need some time to test, please give us more time to have a test and further investigate it, we will post back ASAP.

Appreciate your cooperation in advance. 😊

Best regards,

Gloria