How to use calculated columns in SharePoint Online lists and libraries (Video)

Learn how to create and use calculated columns in SharePoint lists and libraries. Not all calculated columns are mathematical – you could extract text or combine column values as well, for example. See the additional column reference handout for this lesson in the full course at www.NateTheTrainer.com.

This video is part of my FREE 30+ lesson self-paced online training course called Collaboration in Microsoft 365 (OneDrive, SharePoint, and Teams). Enroll today at https://www.NateTheTrainer.com for the full learning experience including lesson discussions, quizzes, exams, and a completion certificate.

You can also watch the entire course as a YouTube playlist as well (just without the course discussions, quizzes, exam, and certificate). Be sure to subscribe to support my channel and for easy access to future content.

Additional resources

How to create one-click, direct download links in modern SharePoint Online libraries

I previously blogged how to create one-click direct download links, but that post was exclusive to the classic experience in SharePoint (or any opportunity in which we could use classic html/css).

Normally, to download a document in modern SharePoint Online libraries, we would have to use a file’s menu (right-click or ellipsis) then choose Download.

Click to enlarge

I was recently challenged to help figure out how to create a single click experience to immediately download a document in modern SharePoint Online libraries and after much trial and error was able to do so using a little bit of JSON in a calculated column.

Here’s how to create a Download link column in modern SharePoint libraries:

1. Create a calculated column (Library Settings > Create Column) named Download and set its formula to =””

Click to enlarge

2. Copy and paste the following JSON code into the JSON formatting field of the calculated column settings.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "a",
  "txtContent": "Download",
  "attributes": {
    "target": "_blank",
    "href": "=@currentWeb+'/_layouts/15/download.aspx?sourceurl='+[$FileRef]"
  }
}

3. Set the new column to show in the default view so it’s visible in the library and click OK.

Click to enlarge

4. Return to your library, and test it out!

And if you want anonymous users to be able to use these convenient download links, be sure to share a FOLDER link with the anonymous user(s) so they will see the file in the library alongside the one-click download link.

Optional formatting of the Download link

And while I’m not a JSON expert, I did dabble a bit in stylizing the Download link so it would look a little bit better. You can add a little bit of style to the JSON to achieve a more button-like experience (or for the advanced among you, change the element to an actual button or download icon).

Here’s how you can get started stylizing the text link (see result at bottom):

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "a",
  "txtContent": "Download",
  "style": {
    "background-color": "gray",
    "text-decoration": "none",
    "color": "white",
    "font-size": "14px",
    "padding-left": "5px"
  },
  "attributes": {
    "target": "_blank",
    "href": "=@currentWeb+'/_layouts/15/download.aspx?sourceurl='+[$FileRef]"
  }
}
Click to enlarge

Optional usage of the file’s name instead of “Download”

One small change to the JSON will reference the file’s name for the link instead of using “Download” for all links. Then you could hide Name and just use your Download column.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "a",
  "txtContent": "[$FileLeafRef]",
  "attributes": {
    "target": "_blank",
    "href": "=@currentWeb+'/_layouts/15/download.aspx?sourceurl='+[$FileRef]"
  }
}
Click to enlarge

Add a thumbnail column for documents and media in a SharePoint Online document library

Mark Rackley recently tweeted about the ability to create a calculated column in SharePoint online document libraries that would automatically render thumbnails for documents. In the GIF from his tweet, it shows how this works for media files.

Naturally curious, I had to see how this worked for documents of .docx, .pdf, .pptx, etc. types. What I found is that it only currently supports some file types:

Supported file types (there’s likely even more I didn’t test):

  • Word (.docx)
  • PDF (.pdf)
  • Emails (.msg)
  • Images (.png, .gif, .jpg, etc.)
  • Media (.mp4)

Not-yet-supported file types:

  • Excel (.xlsx)
  • OneNote (.one)
  • PowerPoint (.pptx)

Create a thumbnail column in SharePoint Online document libraries

1. Add a new column to your document library (library settings > Create column).

2. Set the column name to Thumbnail. As for type, you have two options:

  • Leave type as Single line of text. Thanks to Dario Cassinerio for sharing that Single line of text type works as well as (and more simply than) Calculated set to [Title].
  • Mark Rackley suggests sticking with Calculated set to [Title] (see example screenshot) to prevent users from editing the text field in forms.

3. Click OK.

Supported file types will have thumbnails rendered (like .docx and .pdf in the example below) and others will just be blank (like .pptx and .xlsx in the example below).

Click to enlarge

Here is an animation demonstrating the entire process, start to finish using Single line of text as column type:

Click to enlarge

And another animation but using the Calculated column set to [Title] type:

Click to enlarge

Using current date and/or time as default column value in SharePoint

Date and time fields can have a default value of “Today” or “Now” that populates if you don’t enter another value in the field before saving an item. In your date/time column settings: 

  • If you choose type “Date only” you’ll get the current date
  • If you choose “Date and time” you’ll get the current date and time

You can instead check the box for “Use calculated value” and use “=Now()” as the formula to return the exact same result. However, the benefit/difference of using the calculated option with =Now() instead of the “Today’s date” option is that if your users change from “Date only” to “Date and time” type down the road, “=Now()” will include both date and time values already. It doesn’t hurt to have time included and not displayed.

Finally, you can use the calculated value option to do true calculations for things like “Due date” or “Reminder” where you use a formula like:

  • =Now()+7 is exactly a week from the current date and time
  • =Now()+(1/24*n) where you’ll replace “n” with a number of hours from the current time

Note that these values will be static, meaning once the date/time populates, it doesn’t update when you edit the item. It’s merely a default value inserted upon creation if you don’t manually enter a different value.

Also, when using Now() with hourly calculations you’ll want to double-check your time zone settings (Site settings –> regional settings) and adjust your formulas accordingly if you’re unable to identify a fix.

If just using “Date only” type, you can instead use =Today() and not worry about time zone so much unless your calculations will involve units less than one day.

Related topics:

If you need a “live” always-updated value regardless of items being modified, you’ll need to create a separate “Today” column using Microsoft Flow or SharePoint Designer:

This post also includes ideas for calculations using today’s date.

Extract date components using the TEXT() function in SharePoint calculated columns

calculated date.PNG

In your SharePoint form submissions and list items, sometimes you’d like date fields to be displayed a different way for workflows, notifications, views, grouping, additional metadata, etc. See videos at bottom of post for a couple demonstrations.

Just create a calculated column (format as single line of text) in your SharePoint list or library. Then use any of these formulas, replacing [Created] with the name of the date field from which you’re extracting a piece:

To Extract: Use Formula: Example
Year =TEXT([Created],”YYYY”) 2018
Fiscal Year Range (4-digit) =IF(MONTH([Created])>6,YEAR([Created])&”-“&YEAR([Created])+1,YEAR([Created])-1&”-“&YEAR([Created])) 2018-2019
Fiscal Year Range (2-digit) =IF(MONTH([Created])>6,”FY ” & RIGHT(YEAR([Created])+1,2)&”-“&RIGHT(YEAR([Created])+1,2)+1, “FY ” & RIGHT(YEAR([Created])+1,2)-1&”-“&RIGHT(YEAR([Created])+1,2)) FY 18-19
Quarter =”Q” & CHOOSE( MONTH([Date of inspection])   ,1,1,1 ,2,2,2 ,3,3,3 ,4,4,4) Q3
Month (2 digits) =TEXT([Created],”MM”) 08
Month (abbreviation) =TEXT([Created],”MMM”) Aug
Month (full name) =TEXT([Created],”MMMM”) August
Week Number =”Week ” & IF(ROUNDUP((ROUNDDOWN([Created],0)-(DATE(YEAR(ROUNDDOWN([Created],0)),1,1))+WEEKDAY((DATE(YEAR(ROUNDDOWN([Created],0)),1,1))))/7,0)>52,1,ROUNDUP((ROUNDDOWN([Created],0)-(DATE(YEAR(ROUNDDOWN([Created],0)),1,1))+WEEKDAY((DATE(YEAR(ROUNDDOWN([Created],0)),1,1))))/7,0)) Week 35
Day (2 digits) =TEXT([Created],”DD”) 27
Weekday (abbreviation) =TEXT([Created],”DDD”) Mon
Weekday (full name) =TEXT([Created],”DDDD”) Monday

Next-level Tips

  • Build your own date format combining this logic (space and punctuation friendly):
    • =TEXT([Created],”MMMM DD”) for August 27
    • =TEXT([Created],”MMM-YYYY”) for Aug-2018
  • Ampersand (&) joins any strings together.
    • =[Created] & TEXT([Created],”(DDDD)”) will give you Aug-27-2018 (Monday)
    • =”Fiscal Year ” & IF(MONTH([Created])>6,YEAR([Created])&”-“&YEAR([Created])+1,YEAR([Created])-1&”-“&YEAR([Created])) for “Fiscal Year 2018-2019”
    • =[Student Name] & TEXT([Created],” (MMM YYYY)”) gives you Nate Chamberlain (Aug 2018)
  • Experiment with different date formats. YYYY-MM sorts well in lists. I use YYYY-MM (MMM) for clients a lot so it will sort well and also tell you the month name to be crystal clear:
    • =TEXT([Created],”YYYY-MM (MMM)”) gives you 2018-08 (Aug)
  • To prevent “1899” showing up in your calculated column, use an if/then statement to “skip” blank date values or provide default text:
    • =IF([Due Date]<>””,TEXT([Due Date],”YYYY-MM (MMM)”),”No Due Date”)
  • Brackets ([ ]) are not required in formulas for one-word/no-space date fields. Brackets are only needed for “Due Date”, “Start Date” or other multi-word field names. However, they don’t hurt anything if you already have them.
  • You can also use MONTH() and YEAR() to extract just those pieces, but I find the TEXT() function easiest to be able to get exactly what you want and combine multiple values more efficiently.

 

Power BI: Calculate next year’s amount in previous year’s row

next year

The following is a DAX formula you can use to create a calculated column that shows “next year’s” value in “this year’s” row. You can easily adapt this to show “yesterday’s” amount or “tomorrow’s” total as well. It can be modified for days, weeks, months, etc. as long as the time measure is able to be sorted sequentially.

Continue reading “Power BI: Calculate next year’s amount in previous year’s row”

Using today’s date and/or current time in calculated columns and list view filters

I previously shared how to create a “Today” column in SharePoint that would always be up-to-date even if list items weren’t modified. These were no-code solutions that utilized either SharePoint Designer or Microsoft Flow. You can, however, use Today’s date/time to create views and calculated columns without workflow or script or the need to create another column.

Continue reading “Using today’s date and/or current time in calculated columns and list view filters”

Convert Time Zones in Power BI using DAX

timezonecorrection.png

Even if your SharePoint site’s regional settings are correct (or whichever data source you’re pulling from), Power BI could convert it to the wrong time zone upon import. It’s a quick fix, luckily. Instead of using your “modified,” “created” or other date field in your report, we’ll create a new calculated column in Power BI to use with an accurate time zone.

Continue reading “Convert Time Zones in Power BI using DAX”

Automatic iCal (.ics) hyperlinks for SharePoint calendar items using calculated column

ical7

Updated 12/22/19

Important: This concept of using calculated columns will only work as a one-time trick. After adding the column, links will be correct. But because [ID] isn’t meant to be used in calculated columns, it removes itself from the formula if an item is edited after the column has been added. This method is only ideal for one-time uses, or for lists that are not expected to have content additions or changes. See this other post about how to get the same hyperlinks through workflow instead, for a more reliable method.

This project allows users a quick option to save something they see on a SharePoint calendar to their own calendar. Spend 5 minutes on these few instructions and your users will have a convenient way to get involved going forward.

Note that in modern calendar experiences, there’s a built-in “add to calendar” option for event items. This post is for classic experience calendars and calendar items/events.

Basically we’re going to add a calculated text column called iCal which will use the list’s GUID (easy to get, don’t worry) and the specific calendar item’s default ID number to generate a clickable .ics (iCal) file link. Let’s get started!

Continue reading “Automatic iCal (.ics) hyperlinks for SharePoint calendar items using calculated column”