This week I was making a modification to our team site that I thought might be helpful for others in the SharePoint community

More searching didn’t turn up any other solutions. I tried various vbscript/excel functions in the calculated field to no avail. Then I started thinking clientside. I figured that if I could identify the fields in the list display, I should be able to manipulate them with jQuery. A quick search turned up a nice little piece of code by Paul Grenier on EndUserSharePoint.com. He has written a series entitled jQuery for Everyone and one of his articles was on Replacing [Today]. In his article, Paul talks about replacing a DateTime field with an Aging calculation. His article calculates a DateTime from the last modification date.

If you look at the progression we took on these three “versions”, this last one references the “Serial Number” column, which references each of the three date columns (“Year”, “Month”, and “Day”), which each run a function to grab a specific piece of our original date field. Since we’ve converted the original date into a SharePoint serial number, and broken the original date down into its individual pieces, this approach makes the most sense since the work is already being performed – we’re just going to repurpose it when needed.

Once updated, the values displayed on the list should be the same since we’re using the same formula – the only difference is that we’re performing the calculations in separate columns instead of within the actual “DATE” function itself (the logic is still the same though as to what being processed because each referenced column will perform its function then pass its value back to its calling column – the “Serial Number” column).

Just like eBay, I wanted to display the time remaining on auction items. I figured this would be a calculated field (based on a date the user chose for when the auction for that item ended) but having to calculate date differences based on the current date doesn’t work in SharePoint (the elusive [Today] problem). I thought jQuery would help and it did. Here’s how.

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?

In continuance of the series on the “Date and Time” set of functions that I wrote about back at the end of May (that darn “Work” always getting in the way), I’d like to discuss the actual “DATE” function itself in order to help address some of the more frequent questions regarding how to deal with dates in SharePoint.

Hopefully all of this wasn’t too confusing – it’s a hard formula to decipher, but as with all formulas, the best approach is to break it into chunks, determine what each chunk does independently, then merge them all back together and run through the entire thing with a series of test data.

In the next part of this series, we’ll be wrapping things up with a quick revisit to our (now more understandable) formula, and summary.

Probably the best approach to really be able to see just what’s going on is to plug in a few test dates and run through the calculations, which is what we’ll begin with next time.