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.

Related posts:

1. The Truth about using [Today] in Calculated Columns [EDIT] As it’s been pointed out to me the tone...
2. How To Use Filters in SharePoint to show items in the current Calendar Month Its very easy using the Filter feature of SharePoint views...
3. Tip – Showing multiple columns in Planner There may be times when you want to display timeline...