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”””))))
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”””)))) Read More
