Calculate days and hours excluding the weekend.

[happy2023]

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

[#2]

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