0

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 – Amal Hashim Sep 1 '16 at 15:02
1

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.

Workaround

  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 :/ – user1234 Sep 2 '16 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 Sep 2 '16 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? – user1234 Sep 7 '16 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 Sep 7 '16 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 – user1234 Sep 8 '16 at 11:49
0

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 :/ – user1234 Sep 2 '16 at 9:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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