When you normally setup SharePoint Reminder webpart 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, each 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.

Tags: Calculated Columns, Reminder, SharePoint, WebPart