Forum Discussion

happy2023's avatar
happy2023
Copper Contributor
Aug 19, 2024

Calculate days and hours excluding the weekend.

Could you do me a favor?

 

I’m using the SharePoint Calculated field.

 

Here’s what I need: either Initiated or SubLocal can be earlier.

 

The formula works well, but when Initiated or SubLocal falls on a Saturday or Sunday, the formula has some errors. I’ve been working on this task for so long that my brain is refusing to work. Could you help me out?"

 

=IF(OR(ISBLANK(Initiated),ISBLANK(SubLocal)), "",
IF(Initiated<SubLocal,
IF(OR(AND(WEEKDAY(Initiated,2)=6,WEEKDAY(SubLocal,2)=7),AND(WEEKDAY(Initiated,2)=7,WEEKDAY(SubLocal,2)=6)),
"0 days, 00 hrs, 00 mins",
(DATEDIF(Initiated,SubLocal,"D")-INT((DATEDIF(Initiated,SubLocal,"D")+WEEKDAY(Initiated,2))/7)*2)&" days, "&TEXT(MOD(SubLocal-Initiated,1),"hh ""hrs,"" mm ""mins""")),
IF(OR(AND(WEEKDAY(SubLocal,2)=6,WEEKDAY(Initiated,2)=7),AND(WEEKDAY(SubLocal,2)=7,WEEKDAY(Initiated,2)=6)),
"0 days, 00 hrs, 00 mins",
(DATEDIF(SubLocal,Initiated,"D")-INT((DATEDIF(SubLocal,Initiated,"D")+WEEKDAY(SubLocal,2))/7)*2)&" days, "&TEXT(MOD(Initiated-SubLocal,1),"hh ""hrs,"" mm ""mins"""))))

 

 

SharePoint Online

2 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Aug 21, 2024

    happy2023 

    SharePoint’s calculated columns can’t format time values as hh:mm. If you need to include hours and minutes, you may need a custom solution like a Power Automate flow or JavaScript in a custom form.

     

    While SharePoint Calculated Columns are limited, you can still perform basic date difference calculations and adjust for weekends. Below is a simplified version that calculates the number of days between Initiated and SubLocal excluding weekends:

    =IF(OR(ISBLANK([Initiated]), ISBLANK([SubLocal])), "",

    IF([Initiated] < [SubLocal],

        (DATEDIF([Initiated],[SubLocal],"D") - INT((DATEDIF([Initiated],[SubLocal],"D") + WEEKDAY([Initiated],1))/7)*2) & " days",

        (DATEDIF([SubLocal],[Initiated],"D") - INT((DATEDIF([SubLocal],[Initiated],"D") + WEEKDAY([SubLocal],1))/7)*2) & " days"

    ))

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    • happy2023's avatar
      happy2023
      Copper Contributor
      Sep 10, 2024
      Hello.

      Thank you for your reply.

      This one is what I have figured it out. Sine DateDif can't calcuate the exact hours only the day.

      This formulas works.

      =IF(A3<C3,IF(OR(AND(WEEKDAY(A3,2)=6,WEEKDAY(C3,2)=7),AND(WEEKDAY(A3,2)=7,WEEKDAY(C3,2)=6)),"0 days, 00 hrs, 00 mins",
      (DATEDIF(A3,C3,"D")-INT((DATEDIF(A3,C3,"D")+WEEKDAY(A3,2))/7)*2)&" days, "&TEXT(MOD(C3-A3,1),"hh ""hrs,"" mm ""mins""")),
      IF(OR(AND(WEEKDAY(C3,2)=6,WEEKDAY(A3,2)=7),AND(WEEKDAY(C3,2)=7,WEEKDAY(A3,2)=6)),
      "0 days, 00 hrs, 00 mins",
      (DATEDIF(C3,A3,"D")-INT((DATEDIF(C3,A3,"D")+WEEKDAY(C3,2))/7)*2)&" days, "&TEXT(MOD(A3-C3,1),"hh ""hrs,"" mm ""mins""")))