Currently we are still running MOSS. I am going to install 2010 soon and this will be one of the things that will have to be tested. As for this not working, I’m not sure why. All that this does is look at the two date columns and perform a calculation. Are you using the formula exactly the same way as I have written it? It’s possible that one of the column names on your site may be different.

This is my work-around, in case anyone else might find it useful. It displays like yours without your start time. It uses the word “to” to connect the built in start time with the formula end time you created. It will displays the start time, end time, room reserved and room number; the latter 3 choice columns. EX: “1:15 PM to 2:15 PM Computer Lab #2”. There are two formula columns and a number column used here.

1. Create one number column for the room number.

2. Create one calculated column titled Event Time and use this formula: =TEXT([End Time],”h:mm AM/PM”).

4. In the Calendar view select the following columns to display under “Calendar Columns”. Month View Title = Summary, Week View Title = Summary, Day View Title = Summary, leave the rest the default choices.

If you cut and paste from this page you need to replace each of the quotes with regular double quotes. The quotes on this page are converted to wrong quote character.
Thanks for the great tip, worked fine for me.

Hey Beth….For all day events, we have just been booking the time from like 8:00am to 5:00pm. For multiple days, say Monday thru Wednesday, we will book the start date/time for Monday at 8:00am and the end date/time from Wednesday at 5:00pm. This will give the appearance of an all day event booked for the three days.

Trudy…I could not get the default SharePoint start time not to show. This can probably be accomplished through code but for us, no one seems to mind. We have around 8,000 users on our network and I think that it only bothered me that the duplicate time was showing.

Has anybody noticed that if it’s an All Day Event it displays 06:00 PM – 05:59 PM instead of the 12:00 AM – 11:59 PM? I know Eric suggested adding 8:00 – 5:00 and that’s a good workaround, but I have two schedulers and five confernce rooms. It would be awesome if we could tweak the formula. I tried changing the time format to hh:mm vice the orginal h:mm, no joy.

Major problem with that solution and any solution that does date-time calculations in SharePoint.

When SharePoint stores a date/time value, it first passes the value through some type of Time Zone filter which adjusts it to the equivalent value in UTC. When it goes to display a date/time, it passes back through the filter and you see it based on your time settings no matter where you are in the world. Cool!

But all calculations in the “Calculated Columns” are made based on that UTC value without using any such filter and once the calculation is made, it has to store it. So, it happily passes it through the filter to convert the value that is already using UTC into what it thinks should be the UTC equivalent. This is why Tracy noticed that oddity. She was either in CST or MDT when she ran her calculations.

OOOOOPS! Bad programming by Microsoft.

So, what I want to know is this:

Has anybody come up with an adaptable formula to overcome this monstrosity of an oversight?

I’ve been trying to find this solution forever! I wanted a single column to just show Start Time – End Time without dates (i.e. 7:00 AM – 6:00 PM)

I just tried this out and it works perfect!

I got the syntax error on the first try at creating the calculated column- thanks for the pointer to modify the double quotes. SharePoint didn’t like the fancy quotes, so the 2nd time around I copied & pasted the formula to SP, then deleted and re-typed each of the double quotes. It worked like a charm!

I was trying Beth’s solution and keep getting “The formula refers to a column that does not exist.”. I am new to Sharepoint websites so it is all a learning experience for me. I researched that error but it doesn’t make any sense to me. Has anyone else seen this with the calculated fields? Thank you.