Date and Time Column Behavior in SharePoint List/Library

Regional vs. Personal Settings

Frequently, we notice variations in the Date and Time column values among different users. When attempting to retrieve the same Date and Time column value through the Rest API and adjusting for time zones, the returned values can differ.

SharePoint displays date and time values without relying on the user's computer time zone.

SharePoint incorporates two distinct time zone settings that come into play when utilizing datetime columns.

Regional Setting: As the admin, you decide the time zone for everyone on the site. It's like setting the shared clock for all SharePoint users in the site settings.

To modify the regional settings of a SharePoint site.

Open the SharePoint site where you want to change the Time Zone.

Navigate to site content and choose site settings.

In the Site Administration section, find and click on Regional Settings.

Choose the Time Zone you prefer for the site on the page and click OK.

Personal Setting

In your own profile, you get to choose your time zone, and it's your call, even if there's a default set by the admin.

How can you replace the site's regional setting with your personal preferences?

Go to your Microsoft 365 user profile settings. By clicking on My Microsoft 365 User Profile.

You'll be redirected to delve.office.com; there, simply click on "Update Profile".

Under "Contact Information," locate the section on changing language and regional settings. Expand it and click "here".

Click on the three dots (ellipsis) and choose "Language and Region," as shown in the image below.

On the following page, pick "Always use my personal settings," choose your preferred region and time zone, then click "Save All" and close. It may take some time for the changes to take effect.

Let's illustrate this with an example.

In this scenario, we've set up a list and included a Date and Time column. The site settings for this site collection have been configured with the (UTC-08:00) Pacific Time (US and Canada) time zone.

Now, let's see how two different users, User 1 and User 2, each with unique regional settings in their user profiles, encounter this.

User 1. This user hasn't chosen a specific time zone in their user profile, so the site collection regional setting ((UTC-08:00) Pacific Time (US and Canada)) will be applied automatically.

User 2. This user has specified the (UTC+05:30) Chennai, Kolkata, Mumbai, and New Delhi time zones in their user profile, indicating a personalized time preference.

Now, we're adding an item to the list on behalf of "User 1" and including the following values.

For "User 1," the selected date and time during item creation will be displayed as follows

Consider that User 1 inherits the site collection regional setting. Therefore, the selected date and the displayed date will be in (UTC-08:00) Pacific Time (US and Canada).

For "User 2," the displayed date and time will be different because they have set their preferred time zone in their profile. It will show the date and time in the (UTC+05:30) Chennai, Kolkata, Mumbai, and New Delhi time zone, following the chosen locale date format.

So, SharePoint typically stores dates in Coordinated Universal Time (UTC) in its database. However, when displaying dates in a list or other interfaces, SharePoint adjusts the presentation based on the regional settings of the site collection or the personal settings of the user. This ensures that users see dates in their preferred time zone and locale format, even though the underlying storage is in UTC. This approach allows for consistency in data storage while accommodating individual preferences for date and time representation.

Let's explore how SharePoint retrieves the value from a date and time column when accessed in other components of the SharePoint platform.

Using SharePoint REST API

I created a basic web part with a date picker and a submit button. When you click submit, it creates a list item with the selected date. Let's see its behavior when using the REST API.

I'm saving the chosen date in the list. You can see the selected date and its converted ISO string below, which is then stored in the list.

The date retrieved from the list, along with its conversion to a local string as shown below, reveals that the original date differs from what is visible in the list. This disparity occurs because when utilizing the SharePoint REST API, the platform provides the UTC conversion of the date value, rather than adhering to the site collection's regional settings or the user's personal preferences.

List Record

A. We'll see how to make a UTC string to the regional settings of a site collection.

To obtain the offset for the site collection's regional settings, we can utilize the /_api/Web/RegionalSettings/TimeZoneAPI. The resulting response will be as follows.

We have to determine the offset and modify the UTC string accordingly using the following code.

const offset = -(region.Information.Bias + region.Information.StandardBias + region.Information.DaylightBias) / 60.0 - 1;  

moment('2023-12-29T18:30:00Z').utcOffset(offset, false).format() 

We used the moment library to obtain the result, which looks like this.

An alternative approach involves utilizing the API Property FieldValuesAsText to fetch regional or user-specific time settings, and this is how we can use it.

_api/web/lists/getByTitle('ListName')/items?$select=CurrentDate,FieldValuesAsText/CurrentDate&$expand=FieldValuesAsText 

As we can see the initial CurrentDate is in the UTC zone, while FieldValuesAsText/CurrentDate reflects the regional or personal user settings.

Using Power Apps

I've developed a basic canvas app and incorporated a straightforward gallery control. The data source for the gallery is the same list utilized in the SPFx web part. The records used are identical to those created in the web part control.

The default setting in Power Apps displays dates in the local time zone, rather than adhering to regional or personal time zone preferences.

you can use the TimeZoneOffset() function to convert local time to UTC time. The TimeZoneOffset function returns the time zone offset in minutes for the current time zone.

Here, The TimeZoneOffset() function returns -330, which represents the time zone offset between UTC and IST (Indian Standard Time) in minutes. You can use the formula below to obtain UTC time.

DateAdd(ThisItem.CurrentDate,TimeZoneOffset(),TimeUnit.Minutes) 

To display the regional time of the site, you'll need the regional time offset of the site in minutes. Once you have this information, you can use the following formula.

DateAdd(DateAdd(ThisItem.CurrentDate,TimeZoneOffset(),TimeUnit.Minutes),-480,TimeUnit.Minutes)) 

Using Power Automate

I've set up a basic instant cloud flow and linked it to a SharePoint GetItems action, both connected to the same list utilized for other operations.

When using Power Automate to get data from a SharePoint list, the date-time values are usually given in Coordinated Universal Time (UTC). To work with these, you might want to change them to your preferred time zone or format as below.

We can use the simple formula to convert the UTC time into local time.

convertFromUtc(“2023-12-29T18:30:00Z”,'India Standard Time') 

It only needs the respective time zone IDs for the conversion to the desired time zone, and you can find these IDs here.

Now, Let's see the distinct behaviors of date-only and date-time columns.

We added two columns to a list—one for dates only and another for both date and time. We used a Power App to create records in the list. Created a record as shown below.

Here, the entry appears as if it was saved in the form, but when reviewing the record in the list, there may be differences as we can see below.

The Date Only column is meant for storing dates without a time component. When a user chooses and saves a date, SharePoint stores it in Coordinated Universal Time (UTC). This ensures that the recorded date remains consistent globally and is not affected by time zone differences.

However, the DateTime column records both date and time details. When a user picks a date and time, SharePoint saves it in UTC. When displayed, the system adjusts the time according to regional settings. The main distinction is that, unlike the consistent global date, the time part in the DateTime columns changes with different time zones. DateTime columns, including time information, can vary based on the user's time zone.