Saturday, July 03, 2010

Calculate SharePoint Date Columns Excluding Weekends

Sometimes you may need to calculate a SharePoint date column by adding x work days to another date column.
Let's assume you would like to add 3 days since the list item was created to the target date column 'Due Date: Initial Review.'
SharePoint uses Excel type functions making it is easy to add 3 days to date columns but you also need 2 days to compensate for Thursday, Friday, or Saturday, and 1 day if the item was created on Sunday.

  • Created + 3 adds the 3 days to the value in 'Created' column
  • To test the weekday you use: WEEKDAY(Created)=4. In this case testing is on Thursday.
  • If it is Thursday add 2 otherwise add 0 days would look like: If(WEEKDAY(Created)=4,2,0).
  • The entire formula is therefore:
Created+3+IF(OR(WEEKDAY(Created)=4,WEEKDAY(Created)=5,WEEKDAY(Created)=6),2,0)+IF(WEEKDAY(Created)=7,1,0)
Of course, if you have to exceed a full week you will need to work harder to add more than 7 days as more than one weekend might be involved.
Add a Date Calculated Column:

Enter the Formula:


62 comments:

  1. sometimes teh display date shown as 1/1/1900 12:00 Am, when i returned to teh calcuklated column without any change, just press ok and returned to teh list the calculated column shown as well?! why?

    ReplyDelete
    Replies
    1. Is it possible that the source date column had no value, and the value has been applied just before you checked again the value? This can happen for example when the date has been supplied in a workflow.

      Delete
  2. Excellent Post!!!
    With one step i got exact duedate what i want exactly using your formula.
    Thank you!

    ReplyDelete
  3. I have a schedule separated by these "set apart" by the following business workdays [2 9 46 10 10 15 3 5 5 5 15 15 10] I want to put them into a calculated column that will take the start date and automatically populate the remaining business days. Can anyone help???

    ReplyDelete
    Replies
    1. I am not sure I understand the need. Can you explain the goal and provide an example of the expected result?

      Delete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Hi Zeev,
    I need to have "Due Date" column should be automatically calculated from the "Date all docs recd" column +10 Business days. Could you please provide me the exact formula for my requirement.Thanks a ton!

    Cherry

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Hi Cerry, I posted a wrong solution for you but now I corrected it. Since 14 days is exactly 2 weeks, the problematic days would be Sunday and Monday, where you discount these days from the total. The formula; (change the date name into yours):

    =from_date+13-IF(WEEKDAY(from_date)=1,1,0)-IF(WEEKDAY(from_date)=2,2,0)

    Please let me know if this worked for you.

    ReplyDelete
    Replies
    1. Hi Zeev, Thank you so much for your answer. I had work around and the below calculated column worked for me.
      =IF(AND(WEEKDAY([Date all docs rced])>=1,WEEKDAY([Date all docs rced])<2),[Date all docs rced]+12,IF(AND(WEEKDAY([Date all docs rced])>=2,WEEKDAY([Date all docs rced])<=6),[Date all docs rced]+14,[Date all docs rced]+13))

      Thanks,
      Cherry

      Delete
  8. You are correct. My suggestion didn't add the full two weeks for a regular day.
    You can simplify a bit:
    =AND(WEEKDAY([Date all docs rced])>=1,WEEKDAY([Date all docs rced])<2)

    is equivalent to WEEKDAY([Date all docs rced])=1)

    ReplyDelete
  9. Hi Zeev,
    How are you ???

    May you help me, please??

    I´ve been looking for something like what you wrote.

    I got this formula works =[Due Date]=TODAY()+3... however if the user creates a request at Wed, Thu and Fri ??

    Imagine today´s Wednesday and the user wants to withdraw a Notebook, so the user must choose on Monday of the next week.

    If it is Thursday, the user must choose Tuesday...
    If it is Friday, the user must choose Wednesday...

    Always 3 days later by excluding Weekend..

    Do you know how I can do this formula ?

    Thank you !
    Juliano

    ReplyDelete
    Replies
    1. Juliano, if I correctly understand the request, then you can use [Due Date] = TODAY()+3+IF(OR(WEEKDAY(TODAY())=3,WEEKDAY(TODAY())=4,WEEKDAY(TODAY())=5),2,0)+IF(WEEKDAY(TODAY())=6,1,0)
      You need to test because if your weekdays setting is different, try:
      TODAY()+3+IF(OR(WEEKDAY(TODAY())=4,WEEKDAY(TODAY())=5,WEEKDAY(TODAY())=6),2,0)+IF(WEEKDAY(TODAY())=7,1,0)

      Delete
    2. Zeev,
      The formula worked, but does not do what shoud do...

      eg: Today´s Thursday Ok, the next day Due Date must be on Tuesday, perfect it´s happing.

      But, from Wednesday of next week it does not let me choose another date.. even if I choose April..

      Very strange... not ?

      Delete
    3. There is only one date Today(), which is today, taken from the system, the network, wherever. to use as the base date tomorrow or next Wed. you should wait for tomorrow or for Wed, or cheat the system and change the date where SharePoint takes its system variables. So instead of Today() you should use the [column name] and not the function Today().

      Delete
    4. Yeah, I got it.. you are right..

      But, we have to think that the user can create the request any day, but not on the next 3 business days...

      eg: if the user wants to withdraw the notebook on 23/03 (Monday) the formula is now allowing to do it, got it ???

      Today´s Thursday, the user must choose Tuesday (17/03) PERFECT, but from 18/03 any date I choose here, the formula does not alllow me..

      :-(

      Delete
    5. Zeev,

      I´m using this formula..

      =[Data Retirada]=TODAY()+3+IF(OR(WEEKDAY(TODAY())=3,WEEKDAY(TODAY())=4,WEEKDAY(TODAY())=5),2,0)+IF(WEEKDAY(TODAY())=6,1,0)

      Do I have to change anything on this formula ?

      *[Data Retirada] is Due Date, OK ?

      Delete
    6. I think you were talking apples I was hearing bananas. :). Are you referring to a validation rule?

      if any future date should be allowed beyond +3 days + weekend, than you should use >= instead of =,

      Delete
  10. Zeev,

    Sorry about that, didn´t understand your answer...

    But let´s try getting a better understanding...

    Could you please explain me what means:

    WEEKDAY(TODAY())=6),2,0)+IF(WEEKDAY(TODAY())=7,1,0)
    Are these number the weekdays 2,0,7,1,0 ?

    Sunday = 1
    Monday = 2
    Tuesday = 3
    Wednesday = 4
    Thursday = 5
    Friday = 6
    Saturday = 7

    Is it right ?

    ReplyDelete
  11. For example:
    IF(WEEKDAY(TODAY())=7,1,0) translates to:
    if today is Sunday, than return the value 1, otherwise return 0. This is to add one calendar day to the days count.
    similarly,
    IF(WEEKDAY(TODAY())=6,2,0) translates to:
    if today is Saturday, than return the value 2, otherwise return 0. This is to compensate for Sat and Sun. Therefore for 3 days ahead, you need to add to add 2 days also for TODAY())=5 and TODAY())=4 as both fall in a full weekend.

    ReplyDelete
  12. Perfect...

    Now I got everything about the formula and added ">=" and it worked...

    Thank you very much for helping..

    Have you got any another communication media for we talk better ?

    add me on gmail chat. julianoh.souza@gmail.com

    Cheers,
    Juliano

    ReplyDelete
  13. Is there a way to simply add 30 WORKING days to a date? I don’t need count the number of days between two dates, but I do need a date [30 working days hence] to automatically populate a calculated column field based on the manually entered date in another column.

    Scenario: A user enters a date in the “Report Date” column, and the “Reply Date” calculated column would automatically create a date which is the entered “Report Date” + 30 Working Days.

    The below formulas in the “Reply Date” calculated column add 30 calendar days, but they do not add 30 working days.

    =DATE(YEAR([Report Date]),MONTH([Report Date]),DAY([Report Date])+30)

    =[Report Date]+30

    I have observed multiple possible formulas in online forums, but the solutions offered for this type of problem seem to stop at a 7 to 14 day period (1-2 weeks) which is fairly easy to account for, but given that the number of days in a month vary, and they have a different number of weekends, it has been more difficult to find an attempt a 30 workday solution.

    Doubtless there is likely something very obvious I have overlooked, but I haven’t yet identified what that is.

    I would be grateful for any assistance from the fine people who frequent this forum.

    ReplyDelete
  14. Jay,
    The logic is the same.If Sat and Sun should be excluded, and every week has 5 working days + extra 2, than after 6 weeks you get 30 days. Therefor the base # to add is 42.
    (I assume that Monday doesn't count. If counted, the logic is a bit different)
    If you start on Mon, you end up on Monday. The same goes with Tue-Fri. If the user can't enter Sat and Sun, you are all set. Otherwise you add an if condition with the same logic described in the post, adding 1 for Sun to get Mon, and adding 2 for Sat to get Mon.

    ReplyDelete
    Replies
    1. Zeev,

      Thanks for your reply. Yes, the work week is Monday – Friday (I am not concerned about weeding out the holidays). Clearly you understand this better than I; based on what you have just stated, and one of your above solutions, is this any closer?

      Formula for the calculated column “Reply date”:

      =[Report Date]+42-IF(WEEKDAY([Report Date])=1,1,0)-IF(WEEKDAY([Report Date])=2,2,0)

      Thank you in advance for your knowledge and continued patience. You can see that I am new to calculated columns.

      Delete
    2. I think that you should use:
      [Report Date]+41-IF(WEEKDAY([Report Date])=1,1,0)-IF(WEEKDAY([Report Date])=2,2,0)

      It assumes that report date is also counted in the 30 working days. You need to verify that WEEKDAY(Sunday) = 1 because WEEKDAY can be defined differently in different systems. So just check if WEEKDAY(3/1/2015) = 1 just to make sure. (the US dates: March 1st).

      Delete
  15. That worked perfectly, Zeev.

    I know you must get tired of hearing this, but you rock! I really appreciate your help.

    Thank you for your time and knowledge.

    ReplyDelete
  16. Hi Zeev,

    Looking for a replacement formula for a calculated column, knowing that the function WORKDAY doesn't work:

    =IF([Weekend Work?]=TRUE, [Install Start Date]+[Days]-1, WORKDAY([Install Start Date], [Days]-1)

    Many thanks,
    TC

    ReplyDelete
    Replies
    1. TC, It will help if you formulate your calculations in words. Also, what range can Days have?

      Delete
    2. Thanks for the quick reply Zeev!

      I have three columns. One is [Install Start Date], one is [Days], one is [Weekend Work?] (yes/no check box). I wish to calculate a fourth column. It is [Install Finish Date].

      The install will finish the number of days specified after the start date. If weekend work is required then install work will be conducted over the weekend and the finish date should just be an addition of days (weekday and weekend) to start date.

      If however, weekend work is not required then the install will finish after the number of business days from the start date.

      The -1 in the formula is to account for work conducted on the day of install start.

      Days can be whole numbers from 1. Likely to not be more than 365, and usually under 100. Of course the WORKDAY function above (from Excel) will calculate for days = 1 to infinity.

      Thanks
      TC

      Delete
    3. I posted a new post to address the issue you are having. Please check and let me know if it works for you. You will need to incorporate the formula into your if statement:
      http://excel.shilor.org/2015/04/excluding-weekend-days-from-date.html

      Delete
  17. Thanks much for this! I was searching for this formula for days!

    ReplyDelete
  18. This comment has been removed by the author.

    ReplyDelete
  19. Anonymous said...Dear all,

    I am really struggling with getting this formula right. I would like to count the number of working days between two dates to track leadtime:

    =ALS(OF((WEEKDAG(([NS Geinformeerd op:];2))1);(((DATUMVERSCHIL([Ontvangst Offerte aanvraag];[NS Geinformeerd op:];"D")+1))-(AFRONDEN((DATUMVERSCHIL([NS Geinformeerd op:]; [NS Geinformeerd op:];”D”)+1)/7,1)*2)-2);(((DATUMVERSCHIL([NS Geinformeerd op:]; [NS Geinformeerd op:];"D”)+1))-(AFRONDEN((DATUMVERSCHIL([NS Geinformeerd op:]; [NS Geinformeerd op:];"D"+1)/7,1)*2)))

    I keep getting a syntax error. I have the Dutch version so I need to change "," with ";" . I there anything else I need to change. Please help :(

    ReplyDelete
    Replies
    1. I can't read it, but I suggest to do some adjustments and try it in Excel first. In Excel it is easier to check errors.

      Delete
  20. Dear Zeev,
    I was wondering if you could help with a challenge I’m facing, I’ve been trying a number of things but just can’t seem to get it to work as I’m relatively new to this SharePoint stuff.
    I have a task list and want to automatically calculate the due date on creation of the task to show workdays only. However the due date is based on two timelines.
    There is a queue time that can be 0-3 days
    Then there is the actually task completion time 3-5 days.
    In my list I have “Max due date” and “QC started Date”
    So when the item is initially created I want it to calculate the max due date excluding weekends based on the created date, and then recalculate using the QC Started date when its entered
    As an example, I create an task item today 10th Sep, the due date would default to 22nd Sep (3 days Queue time & 5 days task time = 8 working days). However the task is picked up tomorrow 11th sep (1 day Queue time + the 5 days for task completion = 6 working days) , therefore the due date would automatically then recalculate to 17th Sep.
    How do I go about doing this?? I used your variable above and can get that to work based on the created date, but how can I recalculate using the QC started date?
    I hope the above makes sense as I could really use your help.
    Many thanks
    Rich

    ReplyDelete
  21. If the original calculation is correct, I think that it should be simple to adjust. You can use the MAX(create_date, QS date) instead of the create date.

    ReplyDelete
  22. Thanks in advance Zeev this was really helpful. I had another question regarding this. In another column I want to calculate the interval of time from created to actually completed excluding weekends. For example, If a task is created on Friday and is completed on the following Thursday what is the syntax to calculate the interval of time between excluding weekends.

    ReplyDelete
  23. This whole post is about excluding weekends. Please review from start and then adjust to your column names.

    ReplyDelete
  24. Zeev, sorry I guess I didn't explain well. I would like to know what is the interval of time between created and actually completed. For instance, If I have created date, due date, and then actual completed date. What is the syntax to calculate the actual completed date - created date excluding weekends. So if created on 9/25/2015 with a due date of 9/29/2015 and actual completed on 9/30/2015 I would like the number of days in between excluding weekend. Again, sorry if not clear before.

    ReplyDelete
    Replies
    1. Try this function in case your SP version supports it::
      =NETWORKDAYS(earlier_date,later_date) in case it is supported.

      Delete
    2. See also: http://excel.shilor.org/2015/04/excluding-weekend-days-from-date.html

      Delete
  25. What would the calculated column formula be if it is set to 7 working days?

    ReplyDelete
    Replies
    1. This would be a simple math. =fromDate + daysToAdd

      Delete
  26. This comment has been removed by the author.

    ReplyDelete
  27. I am having an issue with the formula not taking weekends into account.
    [RCVD DATE]+3+IF(OR(WEEKDAY([RCVD DATE])=4,WEEKDAY([RCVD

    ReplyDelete
    Replies
    1. sorry did copy all of it
      [RCVD DATE]+3+IF(OR(WEEKDAY([RCVD DATE])=4,WEEKDAY([RCVD DATE])=5,WEEKDAY([RCVD DATE])=6),2,0)+IF(WEEKDAY([RCVD DATE])=7,1,0)

      Delete
    2. If you send an example of the problem, what should have been the correct result, and examples of cases when it works right, and what you are trying to achieve, I may be able to help.

      Delete
  28. I am having an issue with the formula not taking weekends into account.
    [RCVD DATE]+3+IF(OR(WEEKDAY([RCVD DATE])=4,WEEKDAY([RCVD

    ReplyDelete
  29. I am having an issue with the formula not taking weekends into account.
    [RCVD DATE]+3+IF(OR(WEEKDAY([RCVD DATE])=4,WEEKDAY([RCVD

    ReplyDelete
  30. Hi Zeev!
    I can't seem to find my specific scenario and hoping you might help...

    Col A is an upload date (today).
    Col B is a report type. The user can choose either 0,1,2 or 3.
    Col C - I want this to caculate the date in Col A + the number selected in Col B and give a result of added WEEKDAYS.

    Example: date in Col A is 3/29/16.
    Col B choice was 2.
    Col C will be 3/29 plus 2 weekdays = (3/31).
    Does that make sense? Am I asking for too much?? Lol. Any help is appreciated!
    Thanks,
    Melanie

    ReplyDelete
    Replies
    1. I don't have SharePoint to test, but if [Col A] + [Col B] doesn't work because Col B is a selection list, I suggest to run a workflow copying the value in Col B to another number column than you will be able to sum the date field + No-of-days into a date field.

      Delete
  31. This comment has been removed by the author.

    ReplyDelete
  32. Hello Zeev,

    Can you help me with the formula for due date (ie) created date + 10 working days (excluding the week ends and public holidays(for this i have seperate columns called holidays)

    So this formula need to exclude the (saturday,sunday and Public Holiday(Column Name is Holidays) and give the 10th working date from the created date

    ReplyDelete
    Replies
    1. I'm using the formula =Created+10+IF(OR(WEEKDAY(Created)=2,WEEKDAY(Created)=3,WEEKDAY(Created)=4,WEEKDAY(Created)=5,WEEKDAY(Created)=6),3,0)+IF(WEEKDAY(Created)=7,2,0)

      it was calculating the 10th working day from the created date but if some Public holidays present in between it was not excluding those date.. I have the public holidays in seperate list

      Delete
    2. you can make it simpler. Without the holidays Created + 14 is always correct because 10 work days is exactly 14 calendar days. To this you add holidays. If the holidays column contains the number of days to add, than we need to add this number + 2 days of weekend if Created + holidays falls in Sat or Sun. (I assume that the holidays column contains the correct number to exclude, already discounting holidays in the weekend). If you need help on this just let me know how you count the holidays.

      Delete
    3. I have a list call Holiday list... There it have details Like May 1st 2016 will be a holiday etc... If my created date is Today and may 1st falls on weekday and if it between my due date it should exclude that and count my 10 working days

      Delete
    4. I think that you need a column in your list that gets it value from your holiday list with SPD workflow, accumulating only the holiday days that are not weekend days. Then we can calculate in a similar way shown in this post.

      Delete
  33. I need to calculate working days in SharePoint have ( created ,Modified ) Cols excluding Friday and Saturday as weekends

    ReplyDelete
    Replies
    1. See if this post can be helpful for you.
      http://excel.shilor.org/2015/04/excluding-weekend-days-from-date.html

      Delete
  34. Hi Zeev,
    First of all I have to say thank you so much for the information in your blog!

    I saw that you´re really helpfull in the comments and I didnt see any answer that I can use in my case...

    First I calculated the difference between 2 other dates. I have this result in working days and also "normal" days:

    - [inital date] that can be any date;

    - [total of working days] that is the result of working days between 2 other variable dates;

    - [total of days] that is the difference of 2 other variable dates;

    I need a "working calendar date" using [initial date]+[total of working days] or [initial date]+[total of days]. This sum must be only considering working days...

    Example 1 (yyyy-mm-dd):
    Initial date: 2017-08-01
    total of working days: 37
    total of days: 50
    Answer: 2017-09-20

    Example 2:
    Initial date: 2017-04-01
    total of working days: 235
    total of days: 330
    Answer: 2018-02-25

    [Working days] and [total days] both are variables, can be any value, as 10 days, 45 days, 67 days or 131 days...
    The result must be a working date also! hehehe
    I dont care about holidays :-)
    hahaha

    I spent a lot of time researching over net and trying with myself, but I had no success.

    Thanks a lot in advance!
    Rafael Palácio

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. I have no access to SharePoint so I will outline the logic.
      First, I assume that total days has already counted workdays. This means that if initial date and target date are both mon to fri (working days) than it is a simple adding the total days to initial date.
      if so, the problem occurs when initial or initial dates + total days are in weekend.
      if initial is sat, add 2 days, if initial is sun add 1.
      separately do the same for target date:
      if initial + total days is sat add 2, if it is sun add 1.
      checking the weekday has been already explained and can be used.

      Delete

Enter your comment here