I have a calendar with a date & time field called Departure that a user fills when they add an item.

For display purposes, my list view uses a calculated column with =TEXT(Departure,"d-mmm-yy").

Problem is, whenever the date entered in the activity has "Make this an all-day activity that doesn't start or end at a specific hour." checked off, the calculated column displays the incorrect date; it displays 1 day sooner. If the "all-day activity" isn't checked off (and the hours untouched) it displays the correct date.

Is this a bug? Am I doing something wrong? What is the resolution?

  • 1
    This sounds like an issue with time zones. I imagine when you check "Make this an all day activity" it sets the time value of the departure field (which I assume is a renamed "Start Time" or "End Time" field) to 12 AM. When the value is displayed to you it is for some reason getting the time zone modifier applied to it and is showing up earlier.
    – Chloraphil
    Commented Mar 27, 2012 at 19:38
  • SharePoint handles all dates internally as GMT dates. If you do any math on them (format) you have to make sure you apply the correct time offset to them afterwards. SP applies the users time zone automatically on date fields. Commented Jul 4, 2018 at 7:19

1 Answer 1


My guess would be that the problem is because all day events do not have a time so it assumes 12am (and probably assumes it as UTC), but then it is likely correcting for either Daylight Savings or for local time zone. If you add a time mask to your TEXT(), it should show you enough for you to figure out exactly what it is doing.

  • So what is the resolution to this problem? I have my Regional Settings configured under Site Settings. Right now I simply don't use the 'all-day activity', but I would like to be able to since I cannot remove it as an input field. How do I make the date appear correctly?
    – gpresland
    Commented Mar 28, 2012 at 14:15
  • What offset was it showing? DST or UTC?
    – Dave Wise
    Commented Mar 28, 2012 at 14:51
  • Not sure on the timezone as I don't see a way to display the timezone in the date/time format. However, when time is set to 12:00 AM, it appears as 8:00 PM the previous day when 'all-day activity' is checked. My SharePoint is configured to use (UTC-05:00) time.
    – gpresland
    Commented Mar 28, 2012 at 15:26

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.