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   

No comments:

Note to spammers!

Spammers, don't waste your time... all posts are moderated. If your comment includes unrelated links, is advertising, or just pure spam, it will never be seen.