Forum Discussion
Calculate days and hours excluding the weekend.
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.
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""")))