SharePoint Dates are Always Date AND Time

A Quick SharePoint Date Tip!

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.

Times are a Fraction of a Date

Times are represented as parts of a day.

   

Converting a Date and Time to a Date

As you saw 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 Tim