Start Time & Calculated Column Wonkiness in a SharePoint Event List

I was creating some custom rollups on some of our event calendars and came across a very odd bug when trying to grab only the date component of the built-in Start Time field. One's first inclination will be to create a calculated column and give it the formula...

 =[Start Time]

... and then assign its output type to be "Date Only." This works well until a user adds an All Day Event. For reasons unexplainable, the All Day Event flag causes your =[Start Time] to display the date minus one day. Here is an example of this in action:

 

Start Date and Time, Duration, Start Date Value and Start Day are all calculated fields. Notice how the Start Date and Time (=[Start Time]) is reporting 6:00PM of the previous day. The Start Date Value (=[Start Time] - Output Type: Number) confirms this (.75 = 6:00 PM.) Curiously enough, the Duration (=[End Time]-[Start Time]) is properly reporting the duration between 12:00AM and 11:59PM. Why? I don't know. Perhaps it's somehow bound to the regional settings on the site, but I'm not interested in changing a global site setting for the sake of one calculated field.

With this information at our disposal, our calculated column to display the date part of the start date needs to be modified to add one day to the [Start Time] field if an All Day Event is selected. To determine this, we use the Duration above to assume the item is an all-day event and change our formula to be:

=IF(TEXT(([End Time]-[Start Time])-TRUNC(([End Time]-[Start Time]),0),"0.000000000")="0.999305556",[Start Time] + 1, [Start Time])

This will work, but what happens when the user de-selects the "All Day Event" checkbox? The duration stays the same, but all other values begin reporting the correct time:

 

Since our formula above is strictly based on an expected duration, it will add one to the correct date, causing the date 5/11/2010 to appear. Notice though that the raw value of the start time (in this case) is a non-fractional number (40,308) whereas the all-day event was being represented as 6:00 PM (.75) of the previous day. We can use this to add one more nested branch of logic to our calculation:

=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],[Start Time]+1),[Start Time])

I feel somewhat... dirty about having to resort to this kind of calculation in what SHOULD have been a simple =[Start Time] to extract the date part of the Start Time field, but there you have it. Make sure to shower extra longer after having used it.

Published Tuesday, May 11, 2010 9:02 PM by _zekeMouseOver
Filed under:

Comments

# re: Start Time & Calculated Column Wonkiness in a SharePoint Event List

Thursday, May 27, 2010 11:37 AM by TG

Excellent work! Much better than some of the other proposed workaround formulas I've seen, since yours works reliably. If only MS had given us access to the "All Day Event" field...*sigh*. Hopefully SP2010 fixes the start times on All Day Events correctly.

# re: Start Time & Calculated Column Wonkiness in a SharePoint Event List

Thursday, October 6, 2011 4:38 PM by Nicolas

SP2010 (SP1 installed) does not fix this issue.

# re: Start Time & Calculated Column Wonkiness in a SharePoint Event List

Thursday, April 19, 2012 12:32 PM by Jriesen

What really sucks about the whole calendar date is that you can't add this calculated column to the Event content type (which you would want to for doing content roll-ups of calendars) because the display name for the column is Start Time, but the global site column that this column is generated from is named Start Date.  Way to be consistent MS!

# re: Start Time & Calculated Column Wonkiness in a SharePoint Event List

Thursday, January 10, 2013 5:46 PM by Cory Swartz

We had a similar issue with our gantt charts being off a day on the end date and October 2012 CU fixed this.