I discovered an strange behavior when I try to display a date/time in an other format within an calculated column.

The formula of the calculated column is: =TEXT([End Time],"dd‑mmm‑yy") this transforms the value of the column End Time (e.g. 9/4/2016 11:59 PM) to 04‑Sep‑16.

As displayed here: all day

The magic comes in when I set the End Time to an "All Day Event" (due to its an calendar entry). The exact same date/time combination (e.g. 9/4/2016 11:59 PM) gets converted to 05‑Sep‑16 - which is obviously wrong.

not all day

Any ideas why this is happening and how to avoid this conversion error?

  • This might be a bug.. I am not able to repro this in SharePoint Online Commented Sep 1, 2016 at 15:02

2 Answers 2


As far as I can tell, all-day events actually use GMT. I'm on central time, so all-day events are always off by either 6 or 7 hours, depending on daylight savings.


  1. Create a single line of text field to hold a the end date in string format (e.g. Workflow Date)
  2. Set the field using a workflow to the value of End Time returned as a string
  3. Set your calculated date to =TEXT([Workflow Date],"dd‑mmm‑yy")
  • I guess you are right. If I'm using =HOUR([End Time] I get 1 (AM) insted of 11 (PM) which in my case is because I'm in UTC+2 right now. Any suggestion how to handle the problem? I can't just use DAY -1 due to some people working at UTC/GMT as well with this sharepoint list :/
    – flor1an
    Commented Sep 2, 2016 at 10:38
  • The only solution I've found is not using all day events. I'll add a possible solution for your scenario to my answer.
    – Erin L
    Commented Sep 2, 2016 at 13:46
  • thx for that, so u mean the end date should be added twice (the default calandar date and the "custom" workflow date) by the user?
    – flor1an
    Commented Sep 7, 2016 at 14:09
  • Calendar date will be entered by the user. The custom workflow date would be calculated by the workflow, and the user wouldn't need to know it exists.
    – Erin L
    Commented Sep 7, 2016 at 14:27
  • ok I see. Can you give me some details about what to add to the workflow? I cannot find anything calendar related on "actions" within the workflow designer on SharePoint Designer 2013
    – flor1an
    Commented Sep 8, 2016 at 11:49

Chop off all time info

=TEXT( DATE( YEAR([End Time]) , MONTH([End Time]) , DAY([End Time]) ) , "dd‑mmm‑yy" )
  • unfortunately its not working. I even tried to use just =DAY([End Time] and its showing me 5 insted of the expected 4. Very confusing :/
    – flor1an
    Commented Sep 2, 2016 at 9:46

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.