Calculated Column
22 TopicsConcatenating Multi-Choice Field
I have a modern Sharepoint list with Fields including: Speciality - Choice (Multiple selection allowed) SME - Person I'd like to concatenate them into a single line of text. I can't use the Calculated column I believe due to the multiple choice. I tried Flow but really struggling to get anything to work as I keep getting 'Apply to Each' and it won't let me extract the data. Anyone have any suggestions? Many Thanks11KViews0likes3CommentsCalculate a duration in column formatting
How can I calculate a duration in column formatting? With calculated columns, I can calculate a duration in days by subtracting two dates. For example if Start is 9/2/2018 and End is 1/31/2019, End-Start returns 151. With column formatting, End-Start returns 5/31/1970. I understand that I could use both the calculated column and column formatting, but I'd rather do it in one shot. My formatting involves multiple calculations.Solved8.3KViews0likes1CommentFiltering on Calculated Columns not working in SharePoint List
I have a table where i created a calculated column for the Fiscal Year/Quarter = Example: FY2023/Q3. I used to be able to filter on this field but now when i filter on it, it provides 0 (ZERO) results. Any suggestions as to make this work again? Thank you. If i have to, i can create a flow to pull the calculated column into a text column...if I have to...but id rather not.7.5KViews0likes9CommentsUsing calculated formulas for time in lists
I have [Check-In] and [Check-Out] time and date column entries I then have a [Total Time] calculated column which uses this formula =TEXT([Check-In]-[Check-Out],"h:mm") I can get the total time, however I need to be able to set an IF for if the [Lunch Break] column yes/no is "Yes" it will deduct -0.5 or 30mins in the [Total Time] I have attempted a few methods however I keep getting syntax or incorrect values.Solved6.8KViews0likes2CommentsCalculated Column - Blank Values Nested IF Statement
Hello, I could use some assistance with a formula for my calculated column within a SharePoint List. example: column1 column2 output row1 Yes row2 12 No row3 Yes row4 14 No row5 15 15 No row6 Yes I would like the [output] to be the calculated column. if [column1] and [column2] is blank, I would like [output] to "Yes", and if either [column1] or [column2] is blank I would like [output] to "No" I am currently running into the error: "Sorry, something went wrong The formula contains a syntax error or is not supported." Just using this IF statement works =IF(column1="","Yes","No") but if I'd like to add on another or nest it throws that error. could someone please assist? Thanks, MarciroseSolved4.1KViews0likes3CommentsCalculate date based on current years week number and specific week day
Hi, Is there any way to, in a sharepoint list, calculate a date based on 2 columns containing (1)current years week number and (2)a specifik week day. Current year=2023 Column1: 28 Column2: Tis (Tuesday) The calculated date for current year would be 11072023 (DDMMYYY). This is for a planning tool, presented in powerapps, where a great deal of the events repeats every year. In order for me to highlight events in future I need a date. I was able to do it in PowerApps (calculate date based on said columns) but I have problem using/reaching that date more than presenting it, if I had that date in a column from the data source I thought it would be easier.3.5KViews0likes1CommentIF formula error message "The formula contains a syntax error."
Hi all. I'm trying a very simple IF formula in a calculated column at my data list. =IF([Monitorar]=0;"0";"1") I tried replacing the ; by , and not solved. I also tried replacing IF by SE (note that my Microsoft account is in portuguese), and not solved. I tried in another browsers, an nothing different happens.Solved2.6KViews0likes4CommentsModern grouped view not displaying results
I have a Modern library filtered and grouped view that displays "Show All" when the expanded group exceeds 30 items. Upon clicking "Show All" or the group heading itself to view all items in that group, it does not display any results, however it works perfectly in Classic experience. The library has hundreds of document sets – one for each Job Request – so we have created a view that filters for Document Sets only and excludes several values of a Calculated Column (i.e. "Job Status" is not equal to...), then group by the items by the same calculated column ("Job Status"). The problem appears to be grouping using THIS calculated column (contains IF statements) – the same view works when I select any non-calculated column or another concatenated calculated column to group by. When I re-introduce this particular calculated column for grouping (even at a second level) it fails to display results again but again will do so correctly in the Classic Experience! PS: Also, is there some way to configure for more than 30 items before "Show All" appears (PS: I have increased the collapsed number of groups to display value but this doesn't affect Modern "Show All".1.9KViews0likes0CommentsSharePoint calculated field equivalent for AVERAGEIF
Hi everyone, I have list of columns 1-16 that I would like to average. Some of columns are empty and when I use the AVERAGE function it divides by 16 because of my formula. =AVERAGEA(VALUE([Repeat 1]),VALUE([Repeat 2])...,VALUE([Repeat 16])) I got a suggestion to try the ISERROR function to ignore the blanks/0's but it calculates the same way. =SUM( IF( ISNUMBER( VALUE(Column1) ), VALUE(Column1), 0 ), IF( ISNUMBER( VALUE(Column2) ), VALUE(Column2), 0 ), IF( ISNUMBER( VALUE(Column3) ), VALUE(Column3), 0 ), IF( ISNUMBER( VALUE(Column4) ), VALUE(Column4), 0 ), IF( ISNUMBER( VALUE(Column5) ), VALUE(Column5), 0 ), IF( ISNUMBER( VALUE(Column6) ), VALUE(Column6), 0 ) ) / ( IF( ISERROR(Column1 / 1), 0, 1 ) + IF( ISERROR(Column2 / 1), 0, 1 ) + IF( ISERROR(Column3 / 1), 0, 1 ) + IF( ISERROR(Column4 / 1), 0, 1 ) + IF( ISERROR(Column5 / 1), 0, 1 ) + IF( ISERROR(Column6 / 1), 0, 1 ) ) Any help would be appreciated.1.3KViews0likes0Comments