locked
Discrepancy in Calculated Field using Date/Time

    Question

  •  

    I have a calendar item as part of my MOSS 2007 site and would like to show the events coming up in the next 30 days on my home page.  After some research I understand that I cannot filter on the Start Time field when I try to create a view, so I've created a new field called Event Begin that is a calculated field set to =[Start Time].  I can then create my view for the upcoming events by filtering on the calculated Event Begin column.

     

    This works well, except for events that are marked as All Day.  In this case, the value of the Start Time shows midnight as the time (i.e. 1/11/2008 12:00 AM) which is fine, but the value of the Event Begin column shows the time 5 hours earlier (i.e. 1/10/2008 7:00 PM).  I am not subtracting anything when calculating the Event Begin value, so my initial thought is that I have a conflict between Time Zone settings somewhere.

     

    I am Eastern Standard Time here in the US which is GMT-5:00, which would explain the 5 hour difference.  Unfortunately, I cannot figure out where the incorrect Time Zone setting might be.  Within my site the time zone is correct (under Site Settings > Regional Settings), and it is also correct for the application itself in Central Administration (under Central Administration > Application Management > Web Application General Settings).  Is there another place that the Time Zone is set that I am missing?  Am I completely off base and perhaps the discrepancy between the date/time field and the calculated field is caused by something else?

     

    Thanks in advance!

    Friday, January 11, 2008 9:45 PM

Answers

  • Well, this is apparently a known issue as stated in a comment on the Microsoft SharePoint Designer blog (http://blogs.msdn.com/sharepointdesigner/archive/2008/02/25/filtering-and-formatting-with-date-values.aspx), hopefully they'll realize what an issue it is and address it in SP2.

    I did run across a very helpful post that provided a formula that indeed works (http://www.archivum.info/microsoft.public.sharepoint.development_and_programming/2008-07/msg00251.html).  Basically, the formula checks whether the event is an all day event and if so increases the value by whatever you need.  It determines whether the event is all day by checking the difference between the end time and smart time (12:00am to 11:59pm).

    Here's the formula as I used it:

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

    So, get the difference between end time and start time, then subtract the truncated value of the same value (get rid of the value to the left of the decimal which would indicate that the all day event spans multiple days).  If the difference is .999305556 then I'm adding 1 day to the value of start time (since I'm only interested in showing the date, not the time), otherwise leave the date/time as is.

    Hope that helps someone else with calculated date columns.
    • Marked as answer by Chanda Cole Friday, August 22, 2008 5:32 PM
    • Edited by Chanda Cole Friday, August 22, 2008 5:40 PM added hyperlink
    Friday, August 22, 2008 5:32 PM

All replies

  • Hi,

             I am experiencing the same problem. I have been googling on this and have not found a solution to this problem so far.

             Did you ever got a solution to it?

    Thanks
    tatahere
    Friday, July 25, 2008 4:04 AM
  • No, unfortunately I have not found an answer to this yet.  I would have liked to at least been able to make the calculated column  = Start Time unless the All Day Event is checked, otherwise = Start Time plus 5 hours, but I cannot use All Day Event in a calculated column at all, nor can I use it to create a View on the list of only All Day Events.  I'm stuck here, wish I could find a way around this.

    Chanda
    Thursday, July 31, 2008 4:53 PM
  • I have a similar problem on a form that shows the created date and the last modified date.  The list values are correct. 
    But the xpath formula of ddwrt:FormatDate(string(@Modified) ,1033 ,5) reports the same date as being 4 hours later.

    Ex  Modified=10:05 am           ddwrt:FormatDate(string(@Modified) ,1033 ,5)=2:05 pm

    Monday, August 04, 2008 6:28 PM
  • Anyone?
    Thursday, August 07, 2008 5:00 PM
  • Duayne,

    I think this is actually a very big issue with SharePoint, in fact there are many other posts about it as I search the web.  I'm not sure exactly where to post this so that it will actually get some attention from Microsoft - I'll certainly let you know if I find anything more out about it.

    Chanda
    Thursday, August 07, 2008 5:41 PM
  • Well, this is apparently a known issue as stated in a comment on the Microsoft SharePoint Designer blog (http://blogs.msdn.com/sharepointdesigner/archive/2008/02/25/filtering-and-formatting-with-date-values.aspx), hopefully they'll realize what an issue it is and address it in SP2.

    I did run across a very helpful post that provided a formula that indeed works (http://www.archivum.info/microsoft.public.sharepoint.development_and_programming/2008-07/msg00251.html).  Basically, the formula checks whether the event is an all day event and if so increases the value by whatever you need.  It determines whether the event is all day by checking the difference between the end time and smart time (12:00am to 11:59pm).

    Here's the formula as I used it:

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

    So, get the difference between end time and start time, then subtract the truncated value of the same value (get rid of the value to the left of the decimal which would indicate that the all day event spans multiple days).  If the difference is .999305556 then I'm adding 1 day to the value of start time (since I'm only interested in showing the date, not the time), otherwise leave the date/time as is.

    Hope that helps someone else with calculated date columns.
    • Marked as answer by Chanda Cole Friday, August 22, 2008 5:32 PM
    • Edited by Chanda Cole Friday, August 22, 2008 5:40 PM added hyperlink
    Friday, August 22, 2008 5:32 PM
  • Thanks.

    The formula you mention works GREAT for single day events.

    Unfortunately, It doesn't work for all day events that span multiple days.

    This seems like a MAJOR bug in MOSS 2007 that should be fixed.

    I see 2 things that need to be repaired:

    1) The "All Day Event" field should be able to be used in a calculation.   Why it wouldn't be is beyond me.

    2) Using the "start time" and "end time" of an event should return the "start time" and "end time", not a slightly modified version.  

    How is this acceptable?   Do we just expect there to be bugs in Microsoft products, therefore, we spend most of our time trying to find work-arounds for Microsoft bugs?

    • Edited by EdHeal Tuesday, February 03, 2009 7:53 PM Added new lines (\n)
    Tuesday, February 03, 2009 7:52 PM
  • The formula should work for all day events that span multiple days - what is happening for you when you try it?  I just tested here on my system and it seems to work OK, maybe I can help; the section of the formula that subtracts the truncated difference between start and end is supposed to address multi-day events, but I may be doing something differently than you...

    I do think that one thing I found after this posting was that the formulat does not work for events that are recurring - the first event would show up fine but the subsequent instances of that event did not show up and unfortunately I was never able to find a work-around for that.

    I agree with you that the All Day Event field should be available for use in a calculation, and add that to the issue with how dates/times are stored for recurring events the Calendar really does have some major drawbacks when you get into using it pretty heavily.  I hope SP2 will address some of the Calendar shortcomings, and at least the good news is that we should at least have it by April {fingers crossed}.

    Chanda
    Tuesday, February 10, 2009 6:48 PM
  • Thanks Chandra.

    I think I'm 90% +  there.

    Here's what I have:

    StartDate: (Data Type: "Date Only")
    =IF(TEXT(([End Time]-[Start Time])-TRUNC(([End Time]-[Start Time]),0),"0.000000000")="0.999305556",[Start Time]+1,[Start Time])

    EndDate: (Data Type: "Date Only")
    =[End Time]

    DateRange: (Data Type: "Single line of text")
    =IF(TRUNC(StartDate)=TRUNC(EndDate),MONTH(StartDate)&"/"&DAY(StartDate)&"/"&TEXT(StartDate,"yyyy"),MONTH(StartDate)&"/"&DAY(StartDate)&"/"&TEXT(StartDate,"yyyy")&"-"&MONTH(EndDate)&"/"&DAY(EndDate)&"/"&TEXT(EndDate,"yyyy"))

    The DateRange calculated field is correct except where the all-day events cross a calendar month (Ex: Oct 30 - Nov 3).

    Monday, March 23, 2009 6:57 PM
  • It seems that accross month boundaries, it's "0.957638889" instead of "0.999305556".

    Is anyone else seeing that?

    Ed
    Monday, March 23, 2009 7:14 PM
  • I updated the StartDate field with the following formula to take into account events that cross calendar boundaries.

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

    Ed
    Wednesday, March 25, 2009 3:23 PM
  • Nice I found these threads on this issue as I'm running into now still a year and half later (WSS 3.0). I'm seeing that using the suggested formula that an event marked as All Day but spans 11/6 - 11/13 comes up with EdHeal's noted calculation of "0.957638889". So it happens more than at a month boundary - perhaps at a week boundry too. Thanks to all how've worked on this workaround.
    Thursday, October 21, 2010 6:47 PM
  • Ed,

    Your formula worked great on my end.  Thanks for sharing.  I was wondering how to fix this and you provided the answer.  In my sites I have multiple calendars to view and noticed the difference started when the calendar lists were being created using their own site columns that were not in the "Event" list so naturally I was not sure how to fix this.  I had to create the calculated field just to get the text to show correctly without the time stamp on the executive view dashboard. 

    I sort of figured out that using "Start Date" instead of "Start time" created the issue.  apparently they are two totally different ways to view a calendar and thats when the trouble started. 

    Again Thanks for the post.


    WorkerBee09
    Tuesday, February 01, 2011 3:26 PM