Incorrect date/time shows in SharePoint calendar

Question

I am trying to create a column that will display how many days a calendar entry will last for - so for example if we have visitors on site from 25/10/2017 to 26/10/2017 it will display "2".

Currently I have a calculated column in the calendar list, which uses the following calculation:

=DATEDIF([Start Time],[End Time],"D")

This works fine up until the 29th October, when the DST kicks in (I'm in the UK, so it's a 1 hour difference). Because SharePoint stores times in UTC format, it is my understanding that it's messing up and storing an incorrect date for entries listed as "All Day" events.

I amended the formula to:

=IF([Start Time]>DATE(2017,10,29),DATEDIF([Start Time],[End Time],"D")+1,DATEDIF([Start Time],[End Time],"D"))

But this will only work up to the next time the clocks change, and I don't want to have to manually change it every time.

I've tried looking for a solution to this issue, but have so far been unsuccessful, and to be honest I'm kind of new to SharePoint and I'm not really sure about how to edit things on a lower level.

I have checked the timezones on the server and on the site itself, all set to UTC.

Any help would be much appreciated - I can't believe this issue has existed for so long!!

Answer 1

Ok, so after some trial and error I believe I have found a solution to the issue (which may have been better explained here)

Basically I had to create a calculated field for the End Date, date only. To do this I used this calculation:

=IF(TEXT([End Time],"h:mm")="23:59",[End Time]-1/24,IF(TEXT([End Time],"h:mm")="0:59",[End Time]-2/24,[End Time]))

Which takes into account the DST for the UK - I think.

The next step was to create my Day Period calculated column, with the following formula:

=DATEDIF([Start Time],EndDate,"D")+1

I don't know, it seems to work at the moment going past the DST, so I'm happy for now. If it looks like it's not working again in March maybe I'll post back and remove my answer :|

But hopefully it works and is enough to get others by :)

Answer 2

SharePoint behaves based on the timezone you have selected from Site's regional settings. So you don't need to worry about adding a +1 in your formula.