SharePoint Calendar view with date formatting

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

Related posts:

1. SharePoint backup script using PowerShell
2. Windows SharePoint Services Help Search – SPSearch Starting Error
3. Asp.Net MVC convert View to Word Document
4. SharePoint – CSV Default File Action
5. Sharepoint – Getting Photos from People and Groups

Tags: sharepoint