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.
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:
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 video at bottom of post for an example use case and tutorial.
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:
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.
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.
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.
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:
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):
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.
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:
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”.
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!