On one of our recent SharePoint implementations our client, like most, wanted to take advantage of the calendar functionality. They had 2 simple requirements:

  1. Have a separate page for the calendar
  2. List the next 5 events on the homepage

This was very simple to do but the issue we encountered was on the homepage. Whenever a full day event was displayed it was showing a start time of 12am. Since we can’t change this value they didn’t want it to be displayed.

So I thought we could make a simple calculated column (based on [All Day Event]) to display just the date. i.e.

=IF( [All Day Event],
/*Short Start Date*/,
/*Long Start Date Time*/)

=IF( [All Day Event],
;TEXT(DATE(YEAR([Start Time]), MONTH([Start Time]),DAY([Start Time])),"dd/mm/yyyy"),
TEXT([Start Time],"dd/mm/yyyy hh:mm AM/PM"))

Unfortunately we don’t have access to [All Day Event]

So I next thought we could just check the see if the event goes for a multiple of 24 hours (1 day, 2 days, 3 days)

=IF(MOD(ROUNDUP(([End Time]-[Start Time])*24,0),24)=0,
    TEXT(DATE(YEAR([Start Time]), MONTH([Start Time]),DAY([Start Time])),"dd/mm/yyyy"),
    TEXT([Start Time],"dd/mm/yyyy h:mm AM/PM"))

But this solution treats events that start and end at the same time (people do do it) as all day events. So we need to also check for this condition:

=IF(NOT([End Time]=[Start Time]),
    IF(MOD(ROUNDUP(([End Time]-[Start Time])*24,0),24)=0,
        TEXT(DATE(YEAR([Start Time]), MONTH([Start Time]),DAY([Start Time])),"dd/mm/yyyy"),
        TEXT([Start Time],"dd/mm/yyyy h:mm AM/PM")),
    TEXT([Start Time],"dd/mm/yyyy h:mm AM/PM"))

This beast of a calculated column gets the time display we want

You may also like

ASP.NET MVC3 – Application_Error not firing log4net

Recently we have started developing a number of MVC3 applications. They have all started to be released to our staging and production servers and for some reason we are no longer seeing the error emails / log. The best explanation I found for this was on stack overflow.

Keep Reading

Modified properties in LINQ to SQL

I’ve been recently quite surprised to learn that there is no way to tell whether an entity property in LINQ to SQL is updated or not. Sometimes it’s necessary to know which properties are updated before you pass them to the database context for submission. For example in ASP.NET MVC  there might be HTTP POST actions which accept your model object as an action parameter.

Keep Reading

Newsletter sign up

Every couple of months we send out an update on what's been happening around our office and the web. Sign up and see what you think. And of course, we never spam.