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

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.