## 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.

Enter the Formula:

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?

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.

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

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???

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

4. This comment has been removed by the author.

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

6. This comment has been removed by the author.

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.

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

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)

9. Hi Zeev,
How are you ???

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

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)

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 ?

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().

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..

:-(

5. Zeev,

I´m using this formula..

Do I have to change anything on this formula ?

*[Data Retirada] is Due Date, OK ?

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 =,

10. Zeev,

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 ?

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.

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

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.

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.

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.

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).

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.

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

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

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

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

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

18. This comment has been removed by the author.

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 :(

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.

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

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.

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.

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.

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

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

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

26. This comment has been removed by the author.

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

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)

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.

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

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

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

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.

31. This comment has been removed by the author.

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

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

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.

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

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.

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

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

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

Example 2:
Initial date: 2017-04-01
total of working days: 235
total of days: 330

[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.

Rafael Palácio

1. This comment has been removed by the author.

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.