Using calculated column to display SharePoint calendar

Displaying a SharePoint calendar in your intranet/internet site is quite easy. You just add the calendar web part to the page and voilà!

Now, for the most columns in the calendar, this works just fine. To display a Yes for an All Day Event is an option. You don’t like it? Just remove the column from the view. But if you want to use and display All Day Events, the result can be annoying. Check this out:

See that begin and end time of the all day event? It start at 0:00 and ends at 23:59. Now that is pretty ugly to show to your website visitors, isn’t it?

So, what can you do?

One way of tackling this problem is changing the XSLT. But that can be complex and hard to get to work. I’ve chosen a different route and decided to create a calculated column that will render the date and time, depending on if the event is an all day event or not.
Unfortunately, the all day event column (fAllDayEvent) is not available for use in a calculated column. So we need to find out ourselves what an all day event is.

And then I found this site: SharePoint Calendar view with date formatting

It really helped me with the formula to determine what an all day event is.
It did take me some time to realise that the formula contains an error; in the formulas, you need to use a semicolon (;) instead of a comma (,).

First, I made a calculated column (boolean: yes, no) to determine an all day event – AllDay:

=IF(NOT([End Time]=[Start Time]);
  IF(MOD(ROUNDUP(([End Time]-[Start Time])*24;0);24)=0;
    TRUE;
    FALSE);
  FALSE)

Why? So I can use this boolean column in other calculated columns…

Then I made the actual calculated column  I want to display – StartDate:

=IF(AllDay;
  TEXT([Start Time]; "dd-MM-yyyy") & " (all day)";
  TEXT([Start Time]; "dd-MM-yyyy h:mm"))

As you can see, if it’s a regular event, I’ll display date and time.
If it’s an all day event, I’ll display the date, followed by ” (all day)”.

And finally, I change the view of the calendar  web part to display the StartDate column:

 

Much nicer, don’t you think?

Ernst Wolthaus

15 thoughts on “Using calculated column to display SharePoint calendar

  1. Ernst Wolthaus Post author

    Dear Ritz,

    I’ve seen this before. It must have to do with regional settings.
    Anyway, I guess you got it to work eventually? 🙂

    Ernst

    Reply
  2. Jason Crenshaw

    Having some trouble with this on our site. Specifically:
    “MOD(ROUNDUP(([End Time]-[Start Time])*24;0);24”
    is returning 1 for any items in the calendar longer than 2 weeks (e.g. Staff Holidays).

    This is causing the start time to display as 10:00 AM and end time to display as 10:59 AM.

    Any ideas?

    Reply
    1. Ernst Wolthaus Post author

      Hi Jason,

      I’ve tested this on my server and even for a 4 week holiday this works.
      What time do the standard date fields show in the listview?
      They should show 0:00 – 23:59 (or 12:00 AM – 23:59 PM), otherwise this won’t work.

      Now if you copy the formula into Excel, you can ‘debug’ the formula 🙂
      Add 2 date columns (date & time) and and formula. Now you can debug the formula with your dates.

      I did a test on the parts of the formula:

      Start time: 9/19/2013 12:00 AM
      End time 10/16/2013 23:59 PM

      [End Time]-[Start Time] >> returns 27,99930556
      ROUNDUP(([End Time]-[Start Time])*24;0) >> returns 672
      MOD(ROUNDUP(([End Time]-[Start Time])*24;0);24) >> returns 0

      As you can see, the formula isn’t depending on dates, just time. So if the dates have 0:00 and 23:59, it should work.

      Good luck and let me know if you succeeded!
      Ernst

      Reply
      1. Jason Crenshaw

        Ours is displaying the times in 12-hour format, but I would assume the functionality would be the same. Start time is 12:00 AM, end time is 11:59 PM.

        After some further testing, I am more confused than before.

        A test entry I created works correctly when set like this (Dates are in DD/MM/YYYY format – Hello from Australia):

        Start Time: 1/10/2013 12:00 AM
        End Time: 4/10/2013 11:59 PM
        All Day Event: Yes
        AllDay (Calculated Column): Yes

        But when changing the end date one day further I get:
        Start Time: 1/10/2013 12:00 AM
        End Time: 5/10/2013 11:59 PM
        All Day Event: Yes
        AllDay (Calculated Column): No

        So it looks like it might not be only relating to items longer than two weeks, but I can’t see any other differences between the working and not working data.

        Back to the drawing board it seems. 🙂

        Reply
        1. Ernst Wolthaus Post author

          Hi Jason,

          The reason this works is that the subtraction always return something like 23.99930566
          [End Time]-[Start Time] >> returns 27,99930556

          It’s those decimal digits that do the trick.

          Maybe you can just make a column that has =[End time]-[Start time]
          This value should show a value that has 999305555… as decimals.
          If not, try to figure out why… Unfortunately, I cannot help you there. It works on my machine 🙂

          Good luck!
          Ernst

          Reply
          1. Jason Crenshaw

            Thanks for the help Ernst.

            I’ll keep looking into it and I’ll let you know if I figure it out. Looks like it might be a problem with the default start/end time columns, as I made a calculated column set to =[Start Time] and it returned the correct date, but with a time of 10 AM.

            Very strange.

          2. Jason Crenshaw

            Is it possible the 10-hour difference between the inbuilt start time and my calculated start time is caused by our time zone (We are GMT +10)?

  3. Ernst Wolthaus Post author

    Hi Jason,

    All important is the outcome of [End time]-[Start time]. This value should show a value that has 999305555… as decimals.
    If there is a timezone difference, that should be the same for Start Time and End Time. So the value should still be xxx.999305555.

    Have you tried to see what a calculated column produces when you use =[End time]-[Start time] ?

    Reply
    1. Jason Crenshaw

      I have changed my calculated column to [End Time]-[Start Time]

      The test item I’ve created has the following:
      Start Time – 1/10/2013 12:00 AM
      End Time – 4/10/2013 11:59 PM
      Calculated – 3.99930555556057

      If I change the end time:
      Start Time – 1/10/2013 12:00 AM
      End Time – 5/10/2013 11:59 PM
      Calculated – 5.04097222222481

      Reply
      1. Ernst Wolthaus Post author

        Hi Jason,

        That’s strange. I did the same with same dates and get 3.99930555555329 and 4.99930555555329.

        I’m afraid I don’t know why you experience this behaviour.

        Reply
        1. Jason Crenshaw

          Hi Ernst,

          Just thought I would let you know, I figured out my issue.

          It seems that the items that were not working for me we items that started during normal time, and ended during (Australian) daylight savings time.

          This was causing an hour to be remaining when the calclauted column did its thing.

          Thanks for the help!

          Reply
  4. MPT

    Thanks! This was very helpful.

    I was dealing with issues of an all day event showing up with a start date one day earlier. I used the allday boolean to add 1 day if it was an “AllDay” event.

    I also modified it slightly to include the number of days if the event was greater than 1 day. Now it shows (X days) if it is an AllDay event and the difference in days (X) is >1. Else, a single AllDay Event shows as you designed.

    Thank you again. This was a big help

    MPT

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *