Date and Time Myth in SharePoint

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.

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

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.

​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.