SP HAMMAD
Menu

SKILLS & TRICKS

Date and Time Myth in SharePoint

10/19/2018

0 Comments

 
I have come across this issue of Date and Time  in SharePoint that i feel like writing an email to Microsoft for making this the most confusing thing ever . 

When you create a Date column you have the choice of Date and Date & Time.
Picture
Note the keyword “Format” in that option. Even if you select “Date Only”, your users can still type, or copy and paste, a date and a time and it will be stored as a date and time. But… only the date will be displayed.

Time is Fraction of a Date

After considerable amount of wasting my effort and countless guess work, i came to understand that Times are represented as parts of a day.

Converting a Date and Time to a Date

Picture
As mentioned above, SharePoint dates are always dates and times. Even if you set a column to be “Date”, users can still type, or copy and paste, Date and Time values. Columns formatted as “Date” may display as just a date, but they will still be filtered and calculated as a Date and Time.

If you are calculating the number of days between two dates you might write “[Date2]-[Date1]”, which will produce the expected result if both dates are true Date values. If Date2 is “1/16/2018 6:00 AM” and formatted as “1/16/2018”, and Date1 is “1/14/2018” subtracting the two will return 2.25 days, not the hoped for 2 days.

There are several ways of converting a Date and Time to a Date:
  • Use the DATE function:
    DATE( YEAR( [SomeDate] ), MONTH(( [SomeDate] ), DAY( [SomeDate] ) )
  • Use the ROUND function:
    ROUND( [SomeDate], 0) (This will round up or down depending on the value)
  • Use the FLOOR (round down) function:
    FLOOR( [SomeDate], 1)
  • Use the CEILING (round up) function:
     CEILING( [SomeDate], 1)
  • Use the INT function (which also rounds down):
    INT( [SomeDate])
I prefer the INT as it is the least typing and I most often need to round down to remove the fractional part of a Date and Time.

If you are using a third party workflow application like Nintex, please read this thread and it will  help you resolve the issue of wrong time or date in your list.

Date and Time in Microsoft Flow with SharePoint

This is a bit less painful but not straight forward and  even if your tenant is set to your respective time zone, Microsoft Flow will always read date and time in UTC for some odd reason. You should try  using the Date Time - Convert time zone step to convert your date/time to your time zone. I've had to do this in the past and it's really inconvenient, but to me that seems to be the only way to work around the whole UTC format in Microsoft Flow.
Picture
Picture
​Most probably you might still not get it right due to SharePoints own calculation of Date and time so you will need to convert the timestamp in  GTM (+0) etc to get GTM and UTC in the same time zone.

This issue might be resolved but it will arise again when you will come across summer and winter time change. In short this will be a continous pain for you and your team to keep track of date and time. 
0 Comments



Leave a Reply.

    Archives

    February 2020
    January 2020
    December 2019
    November 2019
    October 2019
    September 2019
    August 2019
    May 2019
    April 2019
    March 2019
    February 2019
    January 2019
    December 2018
    November 2018
    October 2018
    July 2018
    June 2018
    May 2018
    April 2018
    March 2018
    November 2017
    October 2017
    April 2017
    March 2017
    February 2017
    January 2017
    November 2016
    July 2016
    December 2014

    Categories

    All
    Agile
    Analysis Of Metrics
    CMS
    DevOps
    Digital Workplace
    Guideline
    Microsoft
    Microsoft Azure
    Microsoft Teams
    Microsoft WPC 2016
    MS Ignite
    Niteco
    Office 365
    Office 365 CDN
    Personalization
    PowerShell
    Project Management
    Scrum
    SharePoint
    SharePoint 2013
    SharePoint 2016
    SharePoint Framework
    Sitecore
    Sitecore 9
    Sitecore Symposium
    Software Project Audit

    RSS Feed

    Picture

Feel free to connect and Subscribe for updates!

SKYPE

hammad.ahmad

Telephone

+62 81316905997

Email

hammad@sphammad.com
  • Home
  • About Me
  • Blog
  • Contact
  • Home
  • About Me
  • Blog
  • Contact