Sandoval's SharePoint Spot

Result = **********

=REPT(”*”,10)

=IF(LEN(Number)<10,REPT(0,(10-LEN(Number)))&Number,TEXT(Number,”0?))

This formula literally steps along as:

If the length of [Number] is less than 10, create an amount of zero’s equaling the difference between 10 and the length of [Number] then display these zero’s followed by the [Number]. If the length of [Number] is not less than 10, display the number using the “TEXT()” formatting option to display it in a number format.

Examples:

Number = 12345
Result = 0000012345

This formula was used with a CorasWorks rollup to display a custom link that says “Click Here”. It does not appear to work as a link in list’s native view.

==================================

=IF(NameCTEcorp<>”",CONCATENATE(“<a target=new href=https://xxxxx.com/sites/sitename/”,NameCTEcorp,”>click here<br>to view</a>”),”")

I often create this additional calculated date column so that I can easily sort content in a chronological order.

The following formula converts a date into a String “YYYY-MM

General Rules Are:

Create a calculated column and input this formula.  I prefer to not have slashes and find periods are easier on the eyes.  You can change the Month and Year to be MM or MMM and YY YYYY respectively.  You can even omit the day.

=TEXT(Date,”mmm.dd.yyyy”)

How do you convert a date into a day of the week?

Create a calculated column that contains…

=TEXT([End Time],”dddd”)

Found this on some blog.  My notes aren’t complete.  But the results are listed.

Q: “I would like to view all entries within the last 30 days or greater.

A: Temporarily create a text column [Today]. You will need to delete this column to see if the formula works. Every time you edit a formula that references [Today] you will have to… “  <don’t know where rest of text is>

“You can now filter a list to show any items where the [calc column] equals one.”

This was used in an Calendar.

“=IF(([Today]-30)>([Start Time]-30),1,0)”

Verify that a cell column contains a sting of text.  This formula basis calculation on cell content vs. the entire cell value.

“=ISNUMBER(FIND(“”BD”",[Column1]))”

Type in the following formula in a Calculated Column. Replace [Column] with the column you want to reference.

=CLEAN([Column])