Working Days, Weekdays and Holidays in SharePoint Calculated Columns

Date:November 21st, 2008 Author: Tags: , , ,
Category: Calculated Columns, SharePoint Ideas, SharePoint Reminder, SharePoint webparts Comments:35 ;

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

We have a list with the following information

ID Title Date Received Reply Due Reply Sent
1 Order hasn’t arrived Friday 7/Nov/08 Wed 12/Nov/08
2 Order missing items Monday 10/Nov/08 Thursday 11/Nov/08

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

Received Weekday Days to add Reply Due
Sunday 1 +4 Thursday
Monday 2 +3 Thursday
Tuesday 3 +3 Friday
Wednesday 4 +5 Monday
Thursday 5 +5 Tuesday
Friday 6 +5 Wednesday
Saturday 7 +5 Thursday

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.

Free SharePoint Calculated Column Cheat Sheet
a handy 3 page reference for calculated column functions and formulas.

Tags: , , ,

35 Responses to “Working Days, Weekdays and Holidays in SharePoint Calculated Columns”

  1. rahulan says:

    thank you very much. solves a few problems.

  2. rgould says:

    Hi, is there anyway to incoroprate the working day into this, and to add hours rather than days?

  3. Ryan says:

    rgould – I am assuming you mean the scenario of “Reply needed in 3 working hours” so if something came in at 4pm Monday then it would be due (9 to 5 working day) by 11am Tuesday?

  4. Mariel says:

    How would the calculated columns work to take the days between [date 1] and [date 2] and exclude the weekends? I’m trying to get the workday duration between the start date and the end date.

  5. Ryan says:

    Mariel – Good question.

    I’ve spent quite some time looking into this an unless you can make some assumptions such as Date1 and Date2 always finish on the same weekday then its just not feasable as far as I can see.

    Excel has the NETWORKDAYS function and at a push you can make this work using DateDiff (NOTE the two f’s!) – but SharePoint has neither.

    You could possibly do this using XSL functions similar to this, but I will have to leave this for others!

    http://sharepointapplied.wordpress.com/2009/04/23/useless-calculated-column-today-trick-xsl-today-to-the-rescue/

  6. Pete says:

    Hi Ryan, I’ve got an issue where I need to calculate the days taken to complete a task and I used this formula:

    =IF([Actual Date Completed]=0,”NA”,TEXT([Actual Date Completed]-[Start Date],”d:h:mm”))

    The formula works almost perfectly….

    My question is, how do I OMIT weekends if the project runs over consecutive weeks?

    Also, can I OMIT times as well? I.e. We work from 8:30am-5:00pm Mon-Fri so there is no need to have times outside these enter the calculation. It’s very important that the amount of time is as accurate as possible.

    I appreciate your help here!

  7. Chris says:

    @Pete, Did you get your answer? It’s a very valid point and I’m facing the same problem!

  8. Triston says:

    I am also interested in working with hours.

    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.

    Thanks.

  9. Ryan says:

    @Pete & @Chris – I think you are pushing the boundaries of what SharePoint formulas can do.

    Depending on your need you could do the calculation client side using JavaScript :-

    http://pathtosharepoint.wordpress.com/2008/08/25/a-countdown-for-tasks-lists/

  10. […] asked in another post: “When a calendar event is being created, I want the end date/time to automatically populate […]

  11. Dave says:

    What about when you have due dates that are 30, 45, or even 60 days out? How do you address the multiple weekends?

  12. Dave says:

    Nevermind figured it out !!!!!

  13. […] I do that? Well I found a great post Working Days, Weekdays and Holidays in SharePoint Calculated Columns that describes how the WEEKDAY formula works and understood that I just needed to change my […]

  14. Damo says:

    To work with hours simple divide by 24.
    Eg. = [MyDatetimeColumn] + 3/24

    Adds 3 hours.

  15. Ryan says:

    @Damo – I’ve written about that here – SharePoint Calculated Columns – adding hours to a date field

  16. Todd Wilder says:

    I blogged about calculating total hours, holiday hours working hours and non-working hours here:

    http://www.spindepth.com/2010/06/calculating-business-hours-in.html

    Basically its too difficult to do all this with calculated columns, so I have a content editor web part that has a button that uses javascript to update a list’s calculations, kind of like a refresh button.

  17. Dave H. says:

    Is 5 business days out this formula?
    =IF(WEEKDAY(Today)>5,Today+7,IF(WEEKDAY(Today)>1,Today+6,Today+7))

  18. Ryan says:

    @Dave – you don’t need to do that. 5 working days always has 2 weekends in it so you can just use [Date]+7 – see “If your due dates are always a multiple of 5” above.

    Also – be sure you understand why you can only use Today in the default value section and not other calculated columns.

  19. Steve says:

    This is what I used to calculate weekdays:

    Calculating the number of weekdays between dates:

    =IF(AND((WEEKDAY([Date Completed],2))1),(((DATEDIF([Date Received],[Date Completed],”D”)+1))-(FLOOR((DATEDIF([Date Received],[Date Completed],”D”)+1)/7,1)*2)-2),(((DATEDIF([Date Received],[Date Completed],”D”)+1))-(FLOOR((DATEDIF([Date Received],[Date Completed],”D”)+1)/7,1)*2)))

    Date Recevied = 05/11/2010 (wednesday), Date Completed = 05/16/2011 (Monday) Formula returns 4

    If you want to this number to show the weekdays that have passed between the dates, add a -1 to the end of the formula.
    =IF(AND((WEEKDAY([Date Completed],2))1),(((DATEDIF([Date Received],[Date Completed],”D”)+1))-(FLOOR((DATEDIF([Date Received],[Date Completed],”D”)+1)/7,1)*2)-2),(((DATEDIF([Date Received],[Date Completed],”D”)+1))-(FLOOR((DATEDIF([Date Received],[Date Completed],”D”)+1)/7,1)*2)))-1

    Date Recevied = 05/11/2010 (wednesday), Date Completed = 05/16/2011 (Monday) Formula returns 3

  20. yecats says:

    Hi Ryan,
    I used the formula to calculate the number of days passed and keep getting an error “#num!”. I think that means I am getting zero “0”. How can I fix it?

    I augmented the initial formula to fit my sharepoint list as follows:
    =(DATEDIF([PRF Completion],[SOW Submitted],”d”))-INT(DATEDIF([PRF Completion],[SOW Submitted],”d”)/7)*2-IF((WEEKDAY([SOW Submitted])-WEEKDAY([PRF Completion]))>0,2,0)+2

    Can you help?

  21. Ryan says:

    Hi Stacey – I can see that the quotes are not quite the right ones – e.g. ” is different to ” (shift + 2 for most people) but not sure if this is just from putting the formula in a comment.

    What I would do to troubleshooting this is to break it down into the individual parts and put each one into a column on its own, this should lead you to which part of the formula is wrong. Also check that your fields [SOW Submitted] [PRF Completion] etc are all DateTime fields and have something in. Good luck!

  22. Jeff says:

    Hello:
    I am using Sharepoint 3.0 to run a project.
    I want to create a webpart that will show the number of workdays remaining on the project. I need to exclude weekends and a certain number of holidays.
    Could you provide me the code to do that or any articles on the web to show me how to do it. I have searched and not found anything. I am new to learning how to use sharepoint.
    Thank you for your time
    Jeff

  23. alex says:

    how will adding 10 business days formula look like?

  24. jason says:

    I am trying to figure out 10 working or business days from a column. and return the date not number.
    received to date to calculate estimated date to ship (10 business days)

  25. Ryan says:

    @Jason – see above in the post where I explain “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” So +10 week days will always be [Date]+14

  26. Anju says:

    Hello All!
    I am struggling with creating a calculated field.
    Our status reports are due on the Friday of every week. The sharepoint list I created is cumulative, week by week, to track our teams tasks an projects. It is working fine. Essentially, what I am trying to do is create a column that posts the date of that week’s Friday, automatically, based on date modified or new postings. This way my boss can filter on only a specific week if she wants. Is this possible? I have tried numerous times, unsuccessfully. I named my column “Status Report Week Ending” and the formula I used is =TODAY()-WEEKDAY(TODAY(),15)+8 (which works exactly as I want it to in excel but not in sharepoint. When I did it as a “Date” field, it returned “5” & “1208” on different lines of entries (not sure what those numbers are referencing). When I switched it to text and added =TEXT(=TODAY()-WEEKDAY(TODAY(),15)+8, “mm dd yyyy”) the output was “NUM!”. I also tried by changing “TODAY()” to “[Modified]” to use the system date/time. Neither worked quite right. What am I doing wrong? Or better yet, is what I am trying even possible in sharepoint?

  27. Ryan says:

    Hi Anju – you can’t use Today in calculated fields in SharePoint
    http://blog.pentalogic.net/2008/11/truth-about-using-today-in-calculated-columns/
    I *think* you can just use Modified wherever you have [today] in your formula, but haven’t checked this.
    =MODIFIED-WEEKDAY(MODIFIED)+8

  28. Zachary says:

    Hi!
    I am fairly new to SharePoint calculations. I saw this calculation as maybe a resolution for my business day calculation, but still not getting the right resolution. I am attempting to get a resolution of 20 business days from a specific date. I am getting calendar days still out of my calculation. Here is what I have in my formula, what am I doing wrong?

    =IF(WEEKDAY([Transfer Date])>3,[Transfer Date]+28,IF(WEEKDAY([Transfer Date])>1,[Transfer Date]+28,[Transfer Date]+28))

  29. Chris says:

    Hi Zachary,

    Because you’re basically doing 4 business weeks, this ought to work for your formula.

    =IF(WEEKDAY([Transfer Date])=7,[Transfer Date]+30,IF(WEEKDAY([Transfer Date])=1,[Transfer Date]+29,[Transfer Date]+28))

  30. hita says:

    Hi Admin3, [Return Date]+5, IF(Weekday([Return Date])>1,[Return Date]+3, [Return Date]+14))

  31. Geof says:

    I am no good at formulas or math I do not understand the statement;
    “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”

    what is =[field}+7? I need calc ate to be 20 days in the future excluding weekends so what am I changing in the formula please?

  32. Geof says:

    OK I see you mean just use -[field]+7 not add this to the other formula. That was not clear to me but get it now.

  33. Debbie says:

    How do I write the isblank formula with the workday cacluation formula for Sharepoint 2010 version?

Leave a Reply

Anti-Spam Quiz:

We need to confirm your email address so we have just sent you an email.


Please click the link in the email and you will have immediate access to the download