254 shaares
9 results
tagged
list
Q: How can I import data from Excel into an existing Task List (or other list) for the various column types using the Datasheet View?
A: The key points are recognizing the column types and formatting the data appropriately in Excel before the attempting the import. Certain column types are not able to be imported in this way, such as the Multi-line Enhanced Text. Let me break it down for you. :)
**** Good example
A: The key points are recognizing the column types and formatting the data appropriately in Excel before the attempting the import. Certain column types are not able to be imported in this way, such as the Multi-line Enhanced Text. Let me break it down for you. :)
**** Good example
Samples for the SharePoint Column Formatting feature to demonstrate different capabilities and possibilities.
Each sample has its own dedicated readme file with a screenshot to show the result of the sample applied to a SharePoint column.
Note: Excel-style expressions are not supported on SP 2019. You need to use the AST(Abstract Syntax Tree) version of the JSON from the sample repository on SP 2019.
Each sample has its own dedicated readme file with a screenshot to show the result of the sample applied to a SharePoint column.
Note: Excel-style expressions are not supported on SP 2019. You need to use the AST(Abstract Syntax Tree) version of the JSON from the sample repository on SP 2019.
In views, you’ll need to set filters using [Today]
You could also create more complicated filters such as:
* Incomplete projects: DueDate <= [Today] AND Completed=No
* Projects beginning in a week or more: StartDate >= ([Today]+7)
* Last year on this day: Created = [Today]-365
In calculated columns, you’ll need to use Today() or Now() as the value for today’s date or today’s date and time.
The difference is Now() uses date and time, so you’ll get down-to-the-minute values (i.e. hours since reported). Today is just date best used for data with no times involved (i.e. days since hire date).
You could also create more complicated filters such as:
* Incomplete projects: DueDate <= [Today] AND Completed=No
* Projects beginning in a week or more: StartDate >= ([Today]+7)
* Last year on this day: Created = [Today]-365
In calculated columns, you’ll need to use Today() or Now() as the value for today’s date or today’s date and time.
The difference is Now() uses date and time, so you’ll get down-to-the-minute values (i.e. hours since reported). Today is just date best used for data with no times involved (i.e. days since hire date).
REST API for a list in SharePoint Online using POSTMAN
Formula for format 999-999-9999:
=AND(
LEN([num])=12,
IF(ISERROR(FIND("-", [num],4)),
FALSE,
(FIND("-", [num], 4) = 4)
),
IF(ISERROR(FIND("-", [num],8)),
FALSE,
(FIND("-", [num], 8) = 8)
),
IF(ISERROR(1*CONCATENATE(MID([num], 1, 3), MID([num], 5, 3), MID([num], 9, 4))),
FALSE,
AND(
1*CONCATENATE(MID([num], 1, 3), MID([num], 5, 3), MID([num], 9, 4)) > 1000000000,
1*MID([num], 1, 3) <> 911,
1*MID([num], 5, 3) <> 911
)
)
)
=AND(
LEN([num])=12,
IF(ISERROR(FIND("-", [num],4)),
FALSE,
(FIND("-", [num], 4) = 4)
),
IF(ISERROR(FIND("-", [num],8)),
FALSE,
(FIND("-", [num], 8) = 8)
),
IF(ISERROR(1*CONCATENATE(MID([num], 1, 3), MID([num], 5, 3), MID([num], 9, 4))),
FALSE,
AND(
1*CONCATENATE(MID([num], 1, 3), MID([num], 5, 3), MID([num], 9, 4)) > 1000000000,
1*MID([num], 1, 3) <> 911,
1*MID([num], 5, 3) <> 911
)
)
)
Formula for field named [Mobile]:
=AND(IF(ISERROR(FIND("(", [Mobile],1)), FALSE, (FIND("(", [Mobile]) = 1)), IF(ISERROR(FIND(")", [Mobile],5)), FALSE, (FIND(")", [Mobile], 5) = 5)), IF(ISERROR(FIND(" ", [Mobile],6)), FALSE, (FIND(" ", [Mobile], 6) = 6)), IF(ISERROR(FIND("-", [Mobile],10)), FALSE, (FIND("-", [Mobile], 10) = 10)),IF(ISERROR(1*CONCATENATE(MID([Mobile], 2, 3), MID([Mobile], 7, 3), MID([Mobile], 11, 4))), FALSE, AND(1*CONCATENATE(MID([Mobile], 2, 3), MID([Mobile], 7, 3), MID([Mobile], 11, 4)) > 1000000000, 1*MID([Mobile], 2, 3) <> 911, 1*MID([Mobile], 7, 3) <> 911)))
Also see, https://thechriskent.com/2012/08/15/validate-phone-number-columns-in-sharepoint/
=AND(IF(ISERROR(FIND("(", [Mobile],1)), FALSE, (FIND("(", [Mobile]) = 1)), IF(ISERROR(FIND(")", [Mobile],5)), FALSE, (FIND(")", [Mobile], 5) = 5)), IF(ISERROR(FIND(" ", [Mobile],6)), FALSE, (FIND(" ", [Mobile], 6) = 6)), IF(ISERROR(FIND("-", [Mobile],10)), FALSE, (FIND("-", [Mobile], 10) = 10)),IF(ISERROR(1*CONCATENATE(MID([Mobile], 2, 3), MID([Mobile], 7, 3), MID([Mobile], 11, 4))), FALSE, AND(1*CONCATENATE(MID([Mobile], 2, 3), MID([Mobile], 7, 3), MID([Mobile], 11, 4)) > 1000000000, 1*MID([Mobile], 2, 3) <> 911, 1*MID([Mobile], 7, 3) <> 911)))
Also see, https://thechriskent.com/2012/08/15/validate-phone-number-columns-in-sharepoint/
Formula for field named [Mobile]:
=AND(IF(ISERROR(FIND("(", [Mobile],1)), FALSE, (FIND("(", [Mobile]) = 1)), IF(ISERROR(FIND(")", [Mobile],5)), FALSE, (FIND(")", [Mobile], 5) = 5)), IF(ISERROR(FIND(" ", [Mobile],6)), FALSE, (FIND(" ", [Mobile], 6) = 6)), IF(ISERROR(FIND("-", [Mobile],10)), FALSE, (FIND("-", [Mobile], 10) = 10)),IF(ISERROR(1*CONCATENATE(MID([Mobile], 2, 3), MID([Mobile], 7, 3), MID([Mobile], 11, 4))), FALSE, AND(1*CONCATENATE(MID([Mobile], 2, 3), MID([Mobile], 7, 3), MID([Mobile], 11, 4)) > 1000000000, 1*MID([Mobile], 2, 3) <> 911, 1*MID([Mobile], 7, 3) <> 911)))
Also see, https://thechriskent.com/2012/08/15/validate-phone-number-columns-in-sharepoint/
=AND(IF(ISERROR(FIND("(", [Mobile],1)), FALSE, (FIND("(", [Mobile]) = 1)), IF(ISERROR(FIND(")", [Mobile],5)), FALSE, (FIND(")", [Mobile], 5) = 5)), IF(ISERROR(FIND(" ", [Mobile],6)), FALSE, (FIND(" ", [Mobile], 6) = 6)), IF(ISERROR(FIND("-", [Mobile],10)), FALSE, (FIND("-", [Mobile], 10) = 10)),IF(ISERROR(1*CONCATENATE(MID([Mobile], 2, 3), MID([Mobile], 7, 3), MID([Mobile], 11, 4))), FALSE, AND(1*CONCATENATE(MID([Mobile], 2, 3), MID([Mobile], 7, 3), MID([Mobile], 11, 4)) > 1000000000, 1*MID([Mobile], 2, 3) <> 911, 1*MID([Mobile], 7, 3) <> 911)))
Also see, https://thechriskent.com/2012/08/15/validate-phone-number-columns-in-sharepoint/
