3 votes
Accepted

Add yrs to Date calculated column not working

You should use this formula: =DATE(YEAR(DatePicture)+5,MONTH(DatePicture),DAY(DatePicture)) It's YEAR(DateColumn)+5 and not YEAR(DateColumn+5). In my test, [DateReported] is a date column:
jleture's user avatar
  • 881
3 votes
Accepted

How to create a calculated column to pull the year of Date column 1, and if blank, pull year from Date column 2, if that's also blank then put Hold

Try using formula like below for your SharePoint calculated column (Year): =IF(ISBLANK([Date1]),IF(ISBLANK([Date2]),"Review",IF(OR(MONTH([Date2])=1,MONTH([Date2])=2,MONTH([Date2])=3),YEAR([...
Ganesh Sanap - MVP's user avatar
2 votes

How to calculate overall score based on columns values?

Use this calculated formula, assuming Choice is your column name =Choice*30 If you need the " points" appended, =CONCATENATE((Choice*30)," points")
thebernardlim's user avatar
2 votes
Accepted

Where am i going wrong with this calculated formula (ISBLANK)?

I think you're missing an extra IF. Try this: =IF(IsBlank([Start of Absence]),"No",IF(DATEDIF([Start of Absence],NOW(),"d")<28,"No","Yes"))
user79408's user avatar
  • 945
2 votes
Accepted

SharePoint Site column of type Single line of text with specific validation expression

AND function in SharePoint Calculated column and validation formulas only support up to 30 parameters/conditions. If you have more than 30 conditions, you can concatenate the multiple conditions use &...
Ganesh Sanap - MVP's user avatar
2 votes
Accepted

SharePoint list Formula to calculate hours NOT WORKING PROPERLY

Try using this formula: =IF(ISBLANK([Column2]),"0",TEXT([Column2]-[Column1],"hh")) Note: Sometimes comma(,) does not work in formula (it is based on language or regional settings ...
Ganesh Sanap - MVP's user avatar
1 vote

How to do an AND statement in amongst nested IF statements

You have to properly open/close the parentheses for AND function like AND(condition_1, condition_2, condition_3). Try using below formula for your SharePoint calculated column: =1-IF([Customer details ...
Ganesh Sanap - MVP's user avatar
1 vote
Accepted

SharePoint List calculation using current date

Use calculated column formula like this: =IF(ISBLANK([Date Closed]),IF([Due Date]<Today(),"Overdue","Open"),"Closed") Notes: Sometimes comma(,) does not work in ...
Ganesh Sanap - MVP's user avatar
1 vote

Show as blank rather than 0.00% (Decimal showing as %)

Try using calculated column formula like this: =IF(ISBLANK([Figure]),"",[Figure]/100) Notes: Sometimes comma(,) does not work in formula (it is based on language or regional settings on ...
Ganesh Sanap - MVP's user avatar
1 vote

Calculated Column next date

Unfortunately, this is not possible using SharePoint calculated column formulas. SharePoint calculated column formulas does not support fetching values from: Lookup column - single selection Lookup ...
Ganesh Sanap - MVP's user avatar
1 vote
Accepted

Count option is unavailable in calculated column

Unfortunately, you cannot show "Totals" of SharePoint list calculated columns. This is a limitation of SharePoint calculated columns. You can find more information and workarounds in below ...
Ganesh Sanap - MVP's user avatar
1 vote

Cannot get past syntax error

NETWORKDAYS() function is not supported in SharePoint calculated formulas. You have to use the formula like: =IF(ISBLANK([Start Date]),"",IF(ISBLANK([End Date]),"",IF([Work ...
Ganesh Sanap - MVP's user avatar
1 vote
Accepted

Sharepoint. ID Column exists in list, and all items view but not in list settings

Unfortunately, Microsoft does not support using ID column in the SharePoint calculated column formulas. You cannot reference the ID of a row for a newly inserted row. The ID does not yet exist when ...
Ganesh Sanap - MVP's user avatar
1 vote

SharePoint calculated column if contains text

Try using this formula for your calculated column: =IF(ISERROR(FIND("Laptop",Title)),IF(ISERROR(FIND("Mobile",Title)),IF(ISERROR(FIND("Outlook",Title)),"","...
Ganesh Sanap - MVP's user avatar
1 vote
Accepted

Calculated column Update daily

Try using this once: =[Certification Expiration Date]-NOW() Also, This is a known behavior of calculated columns that calculated column values are calculated only when: A new item is created An ...
Ganesh Sanap - MVP's user avatar
1 vote
Accepted

How to use Regex in a Calculated Column's formula for finding a string pattern in another column?

Unfortunately, SharePoint calculated column formulas does not support using RegEx in the formulas. You can only use the predefined supported functions in the SharePoint calculated column formulas. You ...
Ganesh Sanap - MVP's user avatar
1 vote
Accepted

Nested if statement not working to create a Risk Log

Your formula just contains additional closing bracket at the end. Try removing by additional bracket from end. Try below formula, it should work: =IF(AND([Initial Likelihood]="Rare",[Initial ...
Kalpesh Vaghela's user avatar
1 vote
Accepted

Creating a Link to Filtered Document Library using JSON Column Formatting

By looking at the JSON you provided, you are searching the document library based on the Title column in the SharePoint list, similar to my answer given at: Filter a document library from a list And I ...
Ganesh Sanap - MVP's user avatar
1 vote
Accepted

Calculated "Average" Column Shows Too Many Decimals

Try using formula like: =ROUND(AVERAGE([Number One]), Two, [Number Three]), 2) Documentations: ROUND function ROUNDUP function ROUNDDOWN function Note: Sometimes comma(,) does not work in formula (...
Ganesh Sanap - MVP's user avatar
1 vote

How to calculate overall score based on columns values?

Extending answer by @thebernardlim further, If you have multiple choice columns with the choices like: 1,2,3,4,5 and you want to get the overall score (your question title mentions it) based on these ...
Ganesh Sanap - MVP's user avatar
1 vote
Accepted

What's wrong with my Calculated Column Formula Syntax?

Your formula syntax looks good to me. Try using comma ( , ) instead of semicolon ( ; ) everywhere in formula like: =IF([Age]<16,"Under 16",IF([Age]<21,"16-20",IF([Age]<31,&...
Ganesh Sanap - MVP's user avatar
1 vote
Accepted

Calculated Formula on Column not working

Try reversing conditions from Seventh Date to First Date like: =IF([Seventh Date]>0,1,IF([Sixth Date]>0,0.9,IF([Fifth Date]>0,0.75,IF([Fourth Date]>0,0.6,IF([Third Date]>0,0.45,IF([...
Ganesh Sanap - MVP's user avatar
1 vote
Accepted

SharePoint Choice Fields

Unfortunately, you cannot use multiple selection choice columns in SharePoint calculated column formula. If you just want to show how many choices are selected/not selected in the list view (not going ...
Ganesh Sanap - MVP's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible