none
Date formatting in Calendars returns wrong day RRS feed

  • Question

  • We have a sharepoint calendar with some all day events (some over multiple days) and we want to display them in an event list with just the start and end dates (not the times). We're doing this using two calculated fields (one for start and one for end) with a return data type of Date and Time, and selecting the Date Only format. We have also tried using a Single line of text return type and using a mask in the formula =TEXT([End Time],"dd/mm/yyyy") 

    The problem is that the end date always displays as the next day to what the actual End Time field is. We output them side by side and they have different values.

    The issue appears to be related to time zones because when we change the calculated field to display with Date & Time then it is 9:59 am the next day (our time zone is +10) for some events and 10:59 (+11) for those events that would occur in daylight savings time. Testing has shown that the Start time is also 10/11 hours out and is actually 10am/11am on the day (not 12am which is the actual Start Time) but this isn't such a big problem for us because the date is correct.

    It only happens on All day events. If we create an event that isn't an All day event, then the calculated end date matches the inbuilt End Time.

    Anyway just wondering if anybody has any clues on why this might happen because just taking a value and putting it in another field should be a pretty straight forward thing to do.

    Thursday, September 5, 2013 6:57 AM

Answers

  • Hi WendyAus,

    This is a reproducible issue, when creating an all day event, the calculate column based on the Start Time/End Time is different from the original column value. The date is stored in SQL in GMT time and is displayed in SP in the correct time in lists. So returning the start time in a calculated column for an all day event returns the start time in GMT time, which is not the current time most likely.

    This is a confirmed issue, as a workaround, I would suggest you to regenerate the calculate column to add one day/minus one day based on whether the event is an all day event, or create a workflow to copy the start time column to your created date/time column.

    To generate the calculate column, instead of using =text[Start Time], use the following format:

    IF(TEXT(([End Time]-[Start Time])-TRUNC(([End Time]-[Start Time]),0),"0.000000000")="0.999305556",IF([Start Time]=ROUND([Start Time],0),[Start Time],DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time])+1)),[Start Time])

    If you choose to create a workflow, please copy the start time column to the created column that just displays the date which will show the correct date, which in the calculated column always showed the previous/out day of the all day event.

    Thanks for your understanding,


    Qiao Wei
    TechNet Community Support

    • Marked as answer by Lindali Sunday, October 6, 2013 1:24 PM
    Thursday, September 26, 2013 6:45 AM
    Moderator
  • The challenge with this issue is that it is linked to Daylight Saving Time which means it is only a problem for six months of the year. A single calculation is going to be wrong for half the year.

    In the UK, the normal calculation (=TEXT([End Time],"dd-mmm") works fine for dates during the Winter months (late October to late March) when the UK Is on GMT. But then shows a day over for Summer months, i.e. once the UK goes to BST (British Summer Time) which is GMT+1. So a single adjustment will simply switch which 6 months of the year the calculation is incorrect for.

    --- Update ---

    Replacing original suggestion.

    Been fixing this today for a client. The easiest solution is to include an IF statement to determine if the End Time includes 0:59 (even though the End Time column view displays as 'date 23:59' if you calculate to test, the result will return 'date+1 0:59' for dates during the daylight saving period).

    The amended formula is =IF((TEXT([End Time],"h:mm")="0:59",[End Time]-1,[End Time])

    This approach displays the correct date throughout the year regardless of whether it is an All Day Event or not, unless an item that does not have 'All Day Event' checked has an ending time intentionally set to 0:59 (quite unlikely).

    I have blogged the method here with additional notes - http://www.sharepointsharon.com/2014/03/sharepoint-calendar-bug-daylight-saving-time/

    --- Update 2 ---

    Just tested using the MS formula and it corrects the date during daylight saving. But at the expense of dates that don't fall within daylight saving. It reverses the problem so that is wrong for the other 6 months of the year...


    SharePoint Sharon





    • Edited by SharePoint Sharon Monday, March 31, 2014 8:26 PM
    • Marked as answer by Lindali Tuesday, April 1, 2014 12:56 AM
    Monday, March 31, 2014 10:08 AM

All replies

  • Hi,

    Base on the description, my understanding is that you want to use PowerShell script to reassign Designer groups with permission of Design instead of Site Designer in all site collections with the its sub-sites.

    The issue is that SP Date only fields specify the time as 12am thus the end date never makes it to the true end date in the calendar view. I suggest to change the format of the calculated column to [End Time]+1.

    In addition, I suggest to use SharePoint Designer workflow and add the action "Add Time to Date". We need add 23 hours to the End Date and then used the action "Set value to value" which tells the workflow to Set the End Date to the outputted Variable: date.  Then set the Workflow to start automatically when an item is created or changed. The event list will show the date span bar correctly.

    Here is a similar thread for you to take a look:

    http://social.technet.microsoft.com/Forums/en-US/1d322956-e8c5-4074-8f06-1950468b3ed1/calendar-view-for-list-wrong-date-interval-for-end-date-last-day

    Best Regards,

    Linda Li


    Linda Li
    TechNet Community Support

    Friday, September 6, 2013 8:26 AM
  • Hi Linda,

    Thanks for the response however I think that at least for some of your response you might be responding to something different because none of the things you mention are described in my post: we're not using Powershell, designer, designer groups, we're not worried about permissions, site collections or subsites.

    I've looked at the link you sent and it is a different scenario to that described. In the case I've documented the issue is related directly to time-zones and some weird stuff going on with the start & end-dates for all day events (ie where it's adding the timezone adjustment) but only when using  calculated fields and the variables that reference them [End Time] & [Start Time].

    I might end up adding a workflow but I really wanted to see if anybody else had encoutered this and what they've done. Though we'll probably just display the time component without using Calculated fields (and hopefully Microsoft will fix it).

    I'm using Sharepoint 2013 OOTB and calculated fields.

    Cheers

    Wendy

    Sunday, September 8, 2013 11:24 PM
  • Hi,

    I understand that the Date formatting in Calendars returns wrong day.

    The issue is that SharePoint Date only fields specify the time as 12am thus the end date never makes it to the true end date in the calendar view. 

    I still suggest to change the format of the calculated column to [End Time]+1.  

    Best Regards,

    Linda Li

                               

    Linda Li
    TechNet Community Support

    Wednesday, September 25, 2013 4:55 AM
  • Linda,

    You are misreading the problem.

    Please note that I am talking about All Day Events where the All Day Event check box is selected. And the built in Start Time and End Time fields (we are NOT using a Date only field).

    In our case, if I display the built in End Time field 11:59 pm is displayed as expected. BUT for exactly the same event a calculated field using [End Time] will display as 9:59 am the next day - obviously these two things should be exactly the same. They are NOT. The difference between 11:59 pm and 9:59 am the next day is 10 hours - this is the timezone offset (during daylight savings [End Time] for all day events will be 10:59am - ie 11 hours).

    This problem does NOT occur for events that don't have the All Day Event check box selected.

    Given what I have described [End Time] + 1 would be two days ahead - it would be 9:59 am of the day after the day after it ended! Of course I could subtract 1 but there is NO way to check that the event is an All Day Event or not. So if I subtracted 1, then events that are NOT All Day Events would be one day early.

    If you're inclined you should be able to see this. In a calendar simply create another End field and set it to [End Time]. Then display both the inbuilt End Time and the calculated End field in a view with Date & Time.

    Wendy

    Wednesday, September 25, 2013 5:27 AM
  • Hi WendyAus,

    This is a reproducible issue, when creating an all day event, the calculate column based on the Start Time/End Time is different from the original column value. The date is stored in SQL in GMT time and is displayed in SP in the correct time in lists. So returning the start time in a calculated column for an all day event returns the start time in GMT time, which is not the current time most likely.

    This is a confirmed issue, as a workaround, I would suggest you to regenerate the calculate column to add one day/minus one day based on whether the event is an all day event, or create a workflow to copy the start time column to your created date/time column.

    To generate the calculate column, instead of using =text[Start Time], use the following format:

    IF(TEXT(([End Time]-[Start Time])-TRUNC(([End Time]-[Start Time]),0),"0.000000000")="0.999305556",IF([Start Time]=ROUND([Start Time],0),[Start Time],DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time])+1)),[Start Time])

    If you choose to create a workflow, please copy the start time column to the created column that just displays the date which will show the correct date, which in the calculated column always showed the previous/out day of the all day event.

    Thanks for your understanding,


    Qiao Wei
    TechNet Community Support

    • Marked as answer by Lindali Sunday, October 6, 2013 1:24 PM
    Thursday, September 26, 2013 6:45 AM
    Moderator
  • The challenge with this issue is that it is linked to Daylight Saving Time which means it is only a problem for six months of the year. A single calculation is going to be wrong for half the year.

    In the UK, the normal calculation (=TEXT([End Time],"dd-mmm") works fine for dates during the Winter months (late October to late March) when the UK Is on GMT. But then shows a day over for Summer months, i.e. once the UK goes to BST (British Summer Time) which is GMT+1. So a single adjustment will simply switch which 6 months of the year the calculation is incorrect for.

    --- Update ---

    Replacing original suggestion.

    Been fixing this today for a client. The easiest solution is to include an IF statement to determine if the End Time includes 0:59 (even though the End Time column view displays as 'date 23:59' if you calculate to test, the result will return 'date+1 0:59' for dates during the daylight saving period).

    The amended formula is =IF((TEXT([End Time],"h:mm")="0:59",[End Time]-1,[End Time])

    This approach displays the correct date throughout the year regardless of whether it is an All Day Event or not, unless an item that does not have 'All Day Event' checked has an ending time intentionally set to 0:59 (quite unlikely).

    I have blogged the method here with additional notes - http://www.sharepointsharon.com/2014/03/sharepoint-calendar-bug-daylight-saving-time/

    --- Update 2 ---

    Just tested using the MS formula and it corrects the date during daylight saving. But at the expense of dates that don't fall within daylight saving. It reverses the problem so that is wrong for the other 6 months of the year...


    SharePoint Sharon





    • Edited by SharePoint Sharon Monday, March 31, 2014 8:26 PM
    • Marked as answer by Lindali Tuesday, April 1, 2014 12:56 AM
    Monday, March 31, 2014 10:08 AM
  • Note that:

    • when you select "All day event", both start and end date are affected.
    • The value is changed depending both on your time zone AND the daylight saving time.
      For example, in Italy:
         24 March Start time is set to 00:00 and interpreted as 24 March 01:00 (no daylight saving)
         24 March End time is set to 23:59 and interpreted as 25 March 00:59 (no daylight saving
         5 April Start time is set to 00:00 and interpreted as 5 April 02:00 (no daylight saving)
         5 April End time is set to 23:59 and interpreted as 6 April 01:59 (no daylight saving)

    So I suggest to correct the formula as follows, to manage the daylight saving settings:

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

    Note that this formula is valid for a time zone set to UTC+1 (Italy).

    Friday, March 24, 2017 10:50 AM
  • @Qiao Wei - I used the calculate column formula you provided above to place in my workflow to report the correct date, however, my End Time still needs adjusting as well since it still shows to be 4 hours behind.

    Can you adjust that formula to fix this issue, please?

    Thanks for your help!


    B. Brown

    Tuesday, August 15, 2017 8:38 PM
  • Were you able to get this to work? I have tried many different solutions, and none have worked thus far. 
    Thursday, October 19, 2017 6:54 PM