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!!