Note: I previously shared how to do this in SharePoint Designer. The following method utilizing Flow is better, and does not use loops/pauses.

It’s well-known that SharePoint calculated columns don’t permit [Today] to be used as a formula for a calculated date column. And the “default to today’s date” setting only works upon creation, and doesn’t update daily. But we can create a standard date column and have Microsoft Flow automatically update it daily for us, therefore allowing us to effortlessly perform calculations against today’s date such as:

  • Age =(TodayDate-Birthday)/365
  • Years of Service =(TodayDate-StartDate)/365
  • Days Past Due =(TodayDate-DueDate)
  • Weeks until summer break =(SummerStart-TodayDate)/7

Here’s how to create your own, always accurate/updated, today column (see bottom of post for video):

Advertisements

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.

headercalendar.PNG

We’ve all been there. One location on a shared calendar will be referred to by multiple people as 20 different things. Johnson Building Room 214 can be entered as “214,” “Johnson 214,” or “J214” to name a few. Canceled events stay on the calendar, sucking up real estate and waiting for someone to delete it manually. Items copied from another calendar make you pay for the convenience of a simple copy and paste by adding the “Copy: ” prefix to the item.

But with a single workflow, we can fix all of these and make our SharePoint calendars look more professional and polished without making more work for end users. This post will cover how we can use workflow to standardize naming of locations with workflow, delete events once they’ve been canceled and get rid of Outlook’s “Copy: ” prefix. You will need SharePoint Designer and appropriate permissions to create workflows to complete the following steps:

calculations

This is a fairly simple solution that takes a date column, compares it to another date and gives you an answer in years (or days, or whatever you want). You’ll need to already have date columns to work with, and if comparing the date to today (years of age, membership, service, etc.) you’ll need a today column (hidden from the view above). This previous post will help you rig a “today” column that is always accurate without needing to update list items manually. Of course all of the following solutions work for any two dates (i.e. day span of vacation request), I’m just sharing specific examples that would involve “Today”.

ical7

Updated 10/17/17

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!