When you normally setup Reminder you can specify to send an email alert when a date is Due In X days or Overdue by X days

When column Service Date is due in 5 days

One fairly common requirement amongst users of Reminder is the ability to set variable ‘Due In’ or ‘Overdue By’ times. For example lets imagine the scenario of a company using SharePoint and Reminder to help control the servicing of various pieces of equipment.

Some equipment may be quite easy to service and replacements would be readily available whilst they are ‘in the shop’. Other equipment could be very complex and much more specialised meaning that we’ve got to arrange the servicing more carefully to ensure its available when needed. For the standard type of equipment perhaps we only need 5 days warning that its due for a service whilst for the specialized equipment we need at least 30 days notice.

Another example a helpdesk system where you are sending out alerts when tickets are still open after a certain time - in other words the Created date is overdue (or in the past) by a certain number of hours or days. For high priority tickets or tickets from certain people you may want to be alerted if they are still unresolved after 1 day - others may be lest time critical and an alert after 2 days is fine.

So how do you do this? Well there are three ways with their own strengths :-

Use an Alert Days column and a calculated column.

Suppose we have a column Service Date we could add a number column called Alert Days where we would enter the number of days notice we need for that record.

We then create a calculated column called Alert Date and with the formula

[Service Date]-[Alert Days]

Beware though, if you have any records with empty Service Date fields then SharePoint will calculate this as “30th December 1899” + [Alert Days] leading to some very overdue records!

To protect against this you should use

=IF( ISBLANK([Service Date]), “” , [Service Date]]+[Alert Days])

Which says If the Service Date is blank then the result is too, otherwise the result is [Service Date]+[AlertDays]. (You could also use a view to filter out any records with an empty Service Date)

We would end up with a list that looked something like this and we would then setup a Reminder web part to watch for an overdue Alert Date.

So event though these two bits of equipment are due to be serviced by the 30th July we will only get an alert about #1 on the 25th (as its a simple bit of equipment) but #2 is more complex we will get an alert 30 days before the service is due to give time to organise everything.

This method has the advantage of flexibility, but it relies upon the person entering data putting in a sensible value in Alert Days.

Tip - This page gives more examples of what you can do with calculated columns and formula - for example adding a number of months to a date.

Use a calculated column that subtracts a different number of days based on a status

This is similar to the above method, but suppose we have a choice column called Service Type that can either be Simple, Average or Complex. Again we want the alerts for Simple equipment servicing to be 5 days in advance, Average to be 10 days and Complex to be 30.

This calculation will return the number of days to add :-

=IF([Service Type]="Complex",30,IF([Service Type]="Average",15,5))

Adding in ISBLANK as shown above gives

=IF(ISBLANK([Service Date]),"",[Service Type] + IF([Service Type]="(Complex",30,IF([Service Type]="(2) Normal",15,5)))

Or broken down for easy reading

=IF(ISBLANK([Service Date]),"",

   [Service Date] +

      IF([Service Type]="Complex",30,

      IF([Service Type]="Average",15,

      5 )))

If the service date is blank then then the result is blank

else the result is [Service Date] +

   + 30 if Complex service
   or + 15 if Average service

   or +5 otherwise

So again we would create a calculated column called Alert Date, use the above formula and setup a Reminder web part to send an email when the Alert Date is Overdue (or Due Today).

The advantage of this method is that you are not relying on users entering the correct figure in an Alert Days column and you can later change everything by updating the formula, suppose you’ve now decided that you need 45 days notice of complex services. The disadvantage is reduced flexibility.

Using multiple Reminder Web Parts

You can setup Views in SharePoint for various subsets of your list (think of 3 views showing simple, average or complex equipment services). You could then setup 3 different reminder web parts set to watch the records in these views and each web part could be set to send an email alert a certain number of days in advance.

The advantage of this method is that any part of the configuration can be different, for example you may wish to send the alerts about complex services to different people or have a different subject line.

A new version (2.5.0) of SharePoint Planner is available to download.

The main change is the interface has been fully localized in French.

In addition :-

• New feature to automatically default the web parts Title to the name of the source list and link to the lists default view (WSS 3.0 and MOSS 2007 only). Both of these settings can be overridden if required using the toolparts Appearance > Title and Advanced > Title URL settings.
• Correction to an error that stopped the By Category view grouping correctly if you were using a User lookup field with presence enabled.

You can simply download and install the new vesion - there is no need to remove the old version first.

The World Time web part is free web part for SharePoint that enables you to show the current date and time in different timezones.

World Time web part

In the last few days we have become aware of a bug that causes it to enter and leave Daylight Savings Time (DST) early, thereby showing the time as an hour too fast (or too slow) for about a week around the time DST starts and ends.

An updated version (1.0.3) that corrects this error is available to download. To upgrade simply install the new version over the top of the old version.

Please accept my apologies for any inconvenience caused - If you want to know the technical details around this problem then read on…

To find out the date that DST time starts and ends the World Time web part uses SharePont’s API and its time zone information for the current site (Microsoft.SharePoint.SPTimeZoneInformation.DaylightDate) which, according to the SharePoint SDK documentation “Gets the date on which daylight time begins for the time zone as a Date object that has day/month/year.”

So for this year it returns a date of “2009-03-02″ for most of the North American time zones. But this year DST starts on the 8th of March, not the 2nd.

Digging deeper this article explains how to modify the Timezone.xml file (where SharePoint stores its time zone information) and explains.

“Day represents the nth week of the month when standard time or daylight saving time starts, where n is a number that represents the transition day. If the transition day occurs on a Sunday, the value 1 indicates the first Sunday of the month. The value 2 indicates the second Sunday of the month, and so on. The value 5 indicates the last Sunday of the month.“

This web part gives a CrossTab view of a SharePoint® list helping you to summarize and analyze data.

(CrossTabs are more commonly known from Microsoft Excel® as a Pivot Tables - http://en.wikipedia.org/wiki/Pivot_table)

There are almost limitless ways to use a CrossTab - here is one quick example

CrossTab - Sales example

We take a Sales list and first generate a CrossTab showing sales by product over time. We can see a big increase in sales from £11,000 in October to £70,000 in November and most of that increase is due to sales of the “Can-o-matic” product.

The next CrossTab shows the same data by Product/Sales Team expressed as a percentage of total sales. We can immediately see that the Regional sales team and the “Can-o-matic” are a winning combination generating 65.4% of revenue.

Previously you would have to export this data to Excel or use MOSS Enterprise Excel Services to perform the same sort of analysis. Now using the CrossTab web part you can summarize and cross tabulate your data for easy analysis in all versions of SharePoint.

This is one of several new products in the pipeline that we are very excited about – if you would like to help with Beta testing then please get in touch

Back in the day of WSS 2 and SPS 2003 you couldn’t use [Today] in Filters.

For example if you tried to setup a filter like this to show items added in the last 7 days

Created is greater than [Today] - 7

You would receive an error “Filter Value is not a valid date” when clicking OK

This lead to all sorts of inventive work arounds including calculated columns to add 7 days onto the Created date and the trick to use [Today] in calculated columns.

After kicking up a bit of a storm with some ill considered remarks in a post explaining the limitations of the [Today] trick I realised I had some of my own old posts that needed updating. Live by the sword…

<tongue-in-cheek> The Truth about using [Today] in Filters </tongue-in-cheek>

In WSS 2 / SPS 2003 you could not use a filter containing a [Today] formula as shown above.

In WSS 3 / MOSS 2007 you can. If you try

Created is greater than [Today] - 7

you see a similar error -

Created is greater than [Today]-7

Yup, that easy.

Just to make clear - you can use other fields. For example to to show all the tasks due in the next 7 days or overdue.

Due Date is less than [Today]+7 AND Status is not equal to Completed

If you are writing CAML queries then see this post about using the OffsetDays attribute.

[EDIT] As it’s been pointed out to me the tone of this article was off from what I intended - the intention is to make clear that this well known and oft mentioned trick does not work as you might expect and to offer alternatives.

If you try to create a calculated column with a formula containing [Today] or [Me] SharePoint gives the error :

“Calculated columns cannot contain volatile functions like Today and Me”

There are a few reasons you might want to do this. One would be to display how many days left you have to complete something, the other is to only show things added in the last X days or due in the next X days.

There are many pages mentioning this workaround :-

1) Create a dummy column called “Today”, then add your calculated column using [Today] in the formula, finally delete the dummy column. If you need to edit the formula then you have to go back to the start and add the dummy column again.

2) Optionally you can create a calculated column called “My Today” and set its formula to =[Today] using the trick above. Reference [My Today] in your other calculated columns. Thus you reference [My Today] which in turn references [Today]. Then you will not have to add the dummy column back in every time you want to edit formulae.

You then pat your self on the back and head home after another successful day of harassing SharePoint to do what you want, Hurrah!

But the next day you take a look at your list and its wrong. The formula has not updated and its still showing yesterdays value. DOH! SharePoint has made a fool of you after all.

Turns around there is actually a good reason for SharePoint not allowing volatile functions like [Today] or [Me] in calculated columns.

“Calculated fields don’t update themselves until item itself is updated.”

Read that again.

The value of a calculated column is only updated when item is edited and in the mean time you will see incorrect calculations every time you look. In fact, for most uses, this trick don’t give you anything more than just referencing the [Modifed] column.

One workaround to the workaround problem is to write a console app to find all these lists that use the [Today] trick and update them automatically once per day. But this workaround to the workaround problem may not be without its own problems that need to be worked around….

Depending on your need there may be a simpler way - turn the problem on its head!

In the example of using a calculated column to filter out items due in x days or overdue SharePoint will accept [Today] as part of the Filter but will not as part of a calculated column - so give it what it wants

[EDIT- WSS 3 / MOSS 2007 now accepts this]

• Use a filter of [Created] is greater than [Today]-7

(Note - its essential to have no spaces; [Today] - 7 will not work but [Today]-7 does.

WSS 2 / SPS 2003

• Create a column called “Filter 7 Days” and formula as =Created+7
• Then use a filter of “Filter 7 Days” is less than [Today]

See this page for more details - http://blog.pentalogic.net/2008/10/advanced-sharepoint-view-and-filters/

So the calculated column is not dynamic (it will never need updating unless the record itself changes) and SharePoint is happy - but the filter is dynamic and you are happy!

If you want a countdown in your view (i.e. Days left to complete a task) then take a look at this post - http://pathtosharepoint.wordpress.com/2008/08/25/a-countdown-for-tasks-lists/

Shameless plug - check this product out if you want “Alert Me” type emails when a task is due soon or overdue.

AcmeCorp aim to reply to customer complaints within 3 working days. They plan to use SharePoint Reminder to alert managers when these targets are not being met.

We have a list with the following information

Once we have this table we could make a View with a filter to only show records that don’t yet have a Reply Sent date (i.e. they have not been dealt with) and then configure a Reminder web part to watch this view and send out emails when the Reply Due date is overdue.

If we didn’t care about working days we could set Reply Due to be a calculated column with a formula of

=[Date Received]+3

The question is - how do we work out the Reply Due date being 3 working days from the received date? (In AcmeCorps case a working week is Monday through Friday)

One of the calculated column functions you can use in SharePoint is Weekday which returns 1 for Sunday, 2 for Monday and through to 7 for Saturday.

So from here we can work out if the day is

Or writing this another way

Weekday > 3 then +5
Weekday = 2 or 3 then +3
Weekday = 1 then +4

or yet another way

if weekday > 3

   add 5

else if weekday > 1

   add 3

else

   add 4

The final ninja move we need is the IF statement

=IF(some expression, do this if true, do this if false)

and these can be nested and combined with AND/OR or even nested with other IF’s - see the documentation.

We can put this into a formula for SharePoint calculated columns like this

=IF(Weekday([Date Received])>3, [Date Received]+5,
IF(Weekday([Date Received])>1,[Date Received]+3,
[Date Received]+4))

Be very careful when entering this sort of formula, if you get the field names wrong or put a space or a bracket in the wrong place then you will see an error.

You can use the above method if your due date is 4,6,7,8,9 etc days on, just adjust the number of days you add as appropriate.

If your due dates are always a multiple of 5 (e.g. 5,10,15) working days then your job is easier as you will always have 2 weekend days involved so you can just add 7,14,21 etc using =[Field]+7

(FREE BONUS! This also works in Excel, remember to change the Date Received to the column/row reference and don’t use square brackets [] and also format the column as a Date)

But what about holidays?

Ahhh, that’s the problem with this method and there is no easy solution without some complex custom workflow code.

One thing that may work for your circumstance is to add a Reply Due column, set it to Date Time type and set the default value to a Calculated Value with the following formula

=IF(WEEKDAY(Today)>3,Today+5,IF(WEEKDAY(Today)>1,Today+3,Today+4))

This will set the Due Date as 3 working days from when the record was entered but it could be overridden manually if there are holiday days between Today and the due date.

Update - an option to use horizontal gridlines has now been built into version 2.4

Alessandro from Italy contacted us wanting to know if there was a way of adding gridlines to SharePoint Planner so it’s easier to visually connect up the column on the left with the corresponding timeline.

With a few Cascading Style Sheets (CSS) ninja moves you can change the display from something like this.

The following snippet does the trick and can be added to an individual page using a Content Editor Web Part thereby changing just the Planner web parts that appear on that page.

DIV.SharePointPlanner2 TABLE.plgx_Default TR TD
{
border-bottom : 1px solid #B9D1F4;
}

Alternatively you can add it to the SharePointPlanner.css file that all planner web parts use or download one that’s already been edited. There can be multiple copies of this file depending on how you install SharePoint and Planner and they can be in different places so it’s best to do a file search and replace them all.

If you are a CSS Ninja then you can make other changes to the appearance and you will find some helpful comments in the CSS file to guide you.

But remember - if you reinstall or upgrade to a later version of Planer you will lose any changes so do keep a copy of your modified files.

We are considering adding this as an option to the configuration tool part so if you think it would be useful then let us know.

And if you’ve got any other questions or suggestions we would love to hear them!

Introduction

We discussed in a previous article how to customise SharePoint’s built in Task List and briefly looked at the power of Views with Filters. In this article we will build upon that by demonstrating some advanced use of Filters.

This article assumes that you are already familiar with Views and Filters - if not please review the previous article.

Overdue Tasks

To show a task lists Overdue tasks use the following Filter :- Status - is not equal to - Completed And Due Date - is less than - [Today] Note - [Today] is a special marker that SharePoint understands as the current date.

My Overdue Tasks

Add the following to the Overdue Tasks filter Assigned To - is equal to - [Me] Note - [Me] is a special marker that SharePoint understands as the current user.

Items added in Last 7 Days

It would be nice to be able to enter something like

• Created - is less than - [Today] - 7

in the filter, but alas its not quite that simple!

[EDIT - This work around was written in early 2006 and WSS 2 / SPS 2003 wouldn't accept this - in WSS 3 / MOSS 2007 you can now use a FILTER formula like [Today]-7 as long as you keep the spaces out!]

The workaround is to create a Calculated column and then filter on that column.

Select Modify Settings and Columns, then Add a new columnGive the column name something like “Filter 7 Days” and select a Calculated column
Enter a suitable DescriptionIn the Formula box enter “=Created+7″Choose Date and Time format and Date Only Clear Add to default view as we don’t want this field to appear on any of the views
Create a new viewSetup a filter to show only :- Filter 7 Days - is greater than or equal to - [Today]

Items Due in the next 7 Days

[EDIT - This work around was written in early 2006 and WSS 2 / SPS 2003 wouldn't accept this - in WSS 3 / MOSS 2007 you can now use a FILTER formula like [Due Date]-7 as long as you keep the spaces out!]

Select Modify Settings and Columns, then Add a new columnGive the column name something like “Filter Coming Due” and select a Calculated column
Enter a suitable DescriptionIn the Formula box enter “=Due Date-7″Choose Date and Time format and Date Only Clear Add to default view as we don’t want this field to appear on any of the views
Create a new viewSetup a filter to show only :- Status - is not equal to - Completed And Filter Coming Due - is less than or equal to - [Today] And Due Date - is greater than or equal to - [Today] This will ensure that the view only displays Uncompleted items that are due in the next 7 Days but are not Overdue.