Hello I want to calculate work days (mon-fri) between to columns in my Sharepoint list. Both columns have date and time. It works for me to calculate days with this formula: "=EndDate-StartDate"But I don't get it to work for working days. I have tried different formulas that I've found online but I get synthax error messages for all of them.... I have Office 365 Sharepoint.. Can anybody help me with this? THANK

This one did it for me:=(DATEDIF([Start Date],[End Date],"D"))-INT(DATEDIF([Start Date],[End Date],"D")/7)*2-IF(WEEKDAY([End Date])<WEEKDAY([Start Date]),2,IF(OR(WEEKDAY([End Date])=7,WEEKDAY([Start Date])=1),1,0))+1I ran it across about 1000 rows in an excel, and the outcome was the same as NETWORKDAYS excel formula across all of them

in screen shot "Print1.png" what column is your calculated column? Here is what your calculation should be if you make a new calculated column given your column names.IF(ISERROR(DATEDIF([Skapad],[Leveransdatum],”d”)),””,(DATEDIF([Skapad],[Leveransdatum],”d”))+1-INT(DATEDIF([Skapad],[Leveransdatum],”d”)/7)*2-IF((WEEKDAY([Leveransdatum])-WEEKDAY([Skapad]))<0,2,0)-IF(OR(AND(WEEKDAY([Leveransdatum])=7,WEEKDAY([Skapad])=7),AND(WEEKDAY([Leveransdatum])=1,WEEKDAY([Skapad])=1)),1,0)-IF(AND(WEEKDAY([Skapad])=1,(WEEKDAY([Leveransdatum])-WEEKDAY([Skapad]))>0),1,0)-IF(AND(NOT(WEEKDAY([Skapad])=7),WEEKDAY([Leveransdatum])=7),1,0))

https://sharepoint.stackexchange.com/questions/197827/calculate-number-of-working-days-between-two-dates

Chris Gullicksen Thank you for your answer but I have tried that one without any luck. I tried it again just now and still I get an error message. I have changed "start date" and "end date" to the names of my columns and I have checked the code but still error. I don't know why. Even if I don't think so but can it be because I have swedish as language in Sharepoint?

So you added a calculated column formatted as text and used the formula ensuring the column names match your column names? (Case and space sensitive) I tested it on a classic calendar and it worked when I looked at the all events list view. Also please give us some more details to the type of list you are using along with some screen shots or more information about the "error" you are getting.

Calculate working days in Sharepoint | Microsoft Community Hub

PradeepKhanna
Copper Contributor
Jun 25, 2020
This one did it for me:
=(DATEDIF([Start Date],[End Date],"D"))-INT(DATEDIF([Start Date],[End Date],"D")/7)*2-IF(WEEKDAY([End Date])<WEEKDAY([Start Date]),2,IF(OR(WEEKDAY([End Date])=7,WEEKDAY([Start Date])=1),1,0))+1
I ran it across about 1000 rows in an excel, and the outcome was the same as NETWORKDAYS excel formula across all of them
- Mark1112
  Copper Contributor
  Sep 15, 2022
  PradeepKhanna
  
  this works, may i know if this can calculate the weekend from start date to end date ?
- AngelEG
  Copper Contributor
  Feb 01, 2021
  Works like a charm, thank you!
- CoutinhoUK
  Copper Contributor
  Aug 26, 2020
  PradeepKhanna
  
  Your formula has worked perfectly for my case, however i was wondering is there is any way to add onto this formula a list of bank holidays days like in Excel with Networking days.
Chris Gullicksen
Iron Contributor
Dec 27, 2019
https://sharepoint.stackexchange.com/questions/197827/calculate-number-of-working-days-between-two-dates
- Revathy147
  Copper Contributor
  Jun 05, 2023
  Hi
  Can you please help me to calculate working days between two dates in Sharepoint. My weekends are Saturdays and Sundays. I tried using your first formula. But I failed. It showed an error. Would you please guide me.
- nima_einarmattsson
  Copper Contributor
  Dec 27, 2019
  Chris Gullicksen
  Thank you for your answer but I have tried that one without any luck. I tried it again just now and still I get an error message.
  
  I have changed "start date" and "end date" to the names of my columns and I have checked the code but still error.
  
  I don't know why. Even if I don't think so but can it be because I have swedish as language in Sharepoint?
  - Chris Gullicksen
    Iron Contributor
    Dec 27, 2019
    So you added a calculated column formatted as text and used the formula ensuring the column names match your column names? (Case and space sensitive) I tested it on a classic calendar and it worked when I looked at the all events list view.
    
    Also please give us some more details to the type of list you are using along with some screen shots or more information about the "error" you are getting.

Forum Discussion

Calculate working days in Sharepoint

Share

Resources