Tristan asked in another post
“When a calendar event is being created, I want the end date/time to automatically populate to 1.5 hours after the start date/time. Seems simple, but haven’t found a formula for this yet”
Sounds like a pretty reasonable suggestion and the Calendar lists NewForm.asx already sets the Start time to the current time – shouldn’t be too hard, right?…
Using the Default Value setting
You can use formula like (1/24th of a day or 1 hour to the rest of us is 0.04167!)
But we can’t use the Created/Modified field in the formula for a new record because it doesn’t exist yet.
OK, so what if we try and use this in the Default Value column as
This will always be ‘Today at 1AM’ rather than ‘Today in exactly 1 hour’ as Today uses 12:00 AM as the time offset. Unfortunately there is no [Now] function in SharePoint.
Its a moot point though as we can’t edit the default value of the Start and End times in a Calendar list anyway.
FAIL! Onto Attempt #2…
When adding a new record to the Calendar SharePoint uses NewForm.aspx so what about modifying that?
We could use SharePoint Designer to customize NewForm.aspx but its quite complex, lots of people don’t like using it and its fraught with potential problems. Sure it gives us much more power but for our purposes its overkill so we are going to get a little hacky. As it turns out its not the cheerleader who’s going to save the World – its jQuery!
- Click “New” in your Calendar to open up NewForm.asxp
- Add &ToolPaneView=2 onto the end of the URL to use a neat workaround to open up the list form in edit mode.
- Add a Content Editor Web Part to the page
- Click Open the toolpane or Edit > Modify Share Web Part
(Tip – if you want to put instructions on your form this is an ideal way to do it – select Rich Text Editor)
A few notes
- You set the number of hours and minutes to add at the top of the script
- You can add more than 24 hours and if you go over a day boundary it will set the end date appropriately.
- If you are doing a lot of this type of thing you may want to check out these two CodePlex projects spjQueryField and SPFF
- If you are going to re-use this script in different areas you should consider placing it in a document library and referencing it using the CEWP “Content Link” as Joels post shows
- This has only been tested on WSS3 & MOSS 2007
|Free SharePoint Calculated Column Cheat Sheet|
|a handy 3 page reference for calculated column functions and formulas.|