I want to format my [Start Time] column as a short date. I created a calculated column and employed the TEXT() method like so:

=TEXT([Start Time], "MM/DD/YYYY")

However, it appears that this is returning and formatting the UTC value of [Start Time]. What I want is to display the formatting in the timezone that pertains to the user's experience.

How do I ask the calculated column to take into account the user's timezone (as captured when the field was created)?

detailed example

If I have a DateTime field set at: 3/21/2017 12:00 AM.

This appears to be stored, however, as: 42814.8333333333 ... which is equivalent to: 3/20/2017 8:00:00 PM UTC.

When I apply the calculated column (above) it outputs: 3/20/2017. Which is not what I want. I want 3/21/2017 ... which should be 42815.

1 Answer 1

 =TEXT( ROUNDUP([Start Time]) , "MM/DD/YYYY")
  • Hi thanks for commenting - I was hoping to find an actual timezone related solution. This will work in the specific example (12:00 AM) however if the time was at 21:00 it would round to the wrong date. SharePoint is obviously managing timezones - was hoping that functionality was available to us little users.
    – thornomad
    Mar 23, 2017 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.