Forum Discussion

nima_einarmattsson's avatar
nima_einarmattsson
Copper Contributor
Dec 27, 2019

Calculate working days in Sharepoint

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-Start...
SharePoint Online
nima_einarmattsson's avatar
nima_einarmattsson
Copper Contributor
Dec 30, 2019

Chris Gullicksen 

 

Yes. I have tried and double tried but still get the error. When I scan through different forums I can see that people get some formulas to work but when I try the same formulas it doesn't work for me. I have attached 3 screenshots. 

 

I hope that you'll see something in them that I'm doing wrong....

Chris Gullicksen's avatar
Chris Gullicksen
Iron Contributor
Dec 30, 2019
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))
  • Muhittin's avatar
    Muhittin
    Copper Contributor
    Apr 14, 2020

    Chris Gullicksen 

    Hello, I have similar situation, may I use your formula, but my weekend days are Friday and Saturday. Kindly expain your formula, so I can understand it and adjust it to match my requirement. Thanks alot.

    This is your formula, please notice the first row, the resuat of one full week came 6 days, where it should be 5 days, if we are concidering the weekend 2 days, please advise.

    • seagle1930's avatar
      seagle1930
      Copper Contributor
      Mar 24, 2022

      MuhittinI have a situation where I have a Go Live date which is a dated field and a project date.

      I need to Subtract 7 days from the go live date. Which is working. However if there is no go live date.

      It shows up as a date of 12/23/1899

      I need when the go live date is populated it populates either blank or - 7 days into the project date.

       

  • nima_einarmattsson's avatar
    nima_einarmattsson
    Copper Contributor
    Jan 02, 2020

    Hello Chris Gullicksen 

     

    I have added a new screenshot and marked the calculated column.

    It works if I just take [Leveransdatum]-[Skapad] (but with this I get total days and not workdays...) but when I take the formula from your message I get the same error message as in "Print3"...

     

    I don't know why.... 

    Can it be a language issue?

     

    Thank you!

    • Chris Gullicksen's avatar
      Chris Gullicksen
      Iron Contributor
      Jan 02, 2020
      I'm not sure, hopefully someone else may be able to shed a little light on this one for you.
      • nima_einarmattsson's avatar
        nima_einarmattsson
        Copper Contributor
        Jan 14, 2020

        Chris Gullicksen 

        I hope so too. 

        So far nobody else have replied. 

        But the thing I don't understand is that the formula seem to work for a lot of users (according to their comments in the forum). So I'm not really sure why it doesn't work for me. 

         

        It would help a lot to get it to work in the Sharepoint view and not have to export to Excel or PowerBI to get the answer 🙂

Resources