• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Working days and hours only

Hi malbarki,

No there is no spl. way. Just copy & past. Just see in the formula bar that the whole formula is enclosed with { ... }. And adjust the refrences.

Regards!
 
Hey guys

I copied, pasted and adjusted all the references of the formula, but I think the problem is the name range I used for the holidays coz I changed it to read from a range of data instead of one cell...Any thoughts on that..

{=(SUMPRODUCT(IF((WEEKDAY(ROW(INDIRECT(INT(E2)&":"&INT(O2))),1)=6)+(WEEKDAY(ROW(INDIRECT(INT(E2)&":"&INT(O2))),1)=7)+(ROW(INDIRECT(INT(E2)&":"&INT(O2)))=Holidays),0,1)*9)-((WEEKDAY(O2,1)<6)*(INT(O2)<>Holidays)*MAX(0,MIN(9,17-MOD(O2,1)*24)))-((WEEKDAY(E2,1)<6)*(INT(E2)<>Holidays)*MAX(0,MIN(9,MOD(E2,1)*24-8))))*60}

Holidays = $D$2:$D$11
 
Last edited:
Hi freinds

Perhaps we can get the exact result result by subtracting from B1-A1 . After that we will have to develop Custom formatting for cell C1.

I had done the same task few months ago but now i have forgot the same.
 
Hi @malbarki !

This has gone a bit tricky, the problem is not with using named range. The problem is with comparison of two array with different dimensions using comparitive operators.
Just see the attached file (Sheet 1)

In column A I had entered Start Date/Time. In Column B I had entered End Date/Time.
In column C I had calculated total time in minutes excluding weekends (Friday & Saturday), any non-official time & holidays using below formula.

=(((SUMPRODUCT(IF((WEEKDAY(ROW(INDIRECT(INT(A2)&":"&INT(B2))),1)=6)+(WEEKDAY(ROW(INDIRECT(INT(A2)&":"&INT(B2))),1)=7),0,1))-SUMPRODUCT(IF(IFERROR(MATCH(ROW(INDIRECT(INT(A2)&":"&INT(B2))),Holiday,0),0),1,0)))*9)-(IF(INT(B2)=IFERROR(INDEX(Holiday,MATCH(INT(B2),Holiday,0)),0),0,((WEEKDAY(B2,1)<6)*MAX(0,MIN(9,17-MOD(B2,1)*24)))))-(IF(INT(A2)=IFERROR(INDEX(Holiday,MATCH(INT(A2),Holiday,0)),0),0,((WEEKDAY(A2,1)<6)*MAX(0,MIN(9,MOD(A2,1)*24-8))))))*60

(Note: This is an array formula so Ctrl+Shift+Enter is must after entering it. Enter in C2 with CSE and copy down)

My Holiday list is in column E. I had used dynamic named range HOLIDAY. I had use below formula to create it.

=Sheet1!$E$2:INDEX(Sheet1!$E$2:$E$10,COUNTA(Sheet1!$E$2:$E$10))

Just check all the possible cases. I had checked some of them. Advise if any issue.

Regards!
 

Attachments

  • Work Day n Time(modified).xlsx
    16.8 KB · Views: 20
Somendra Misra said:
Hi @malbarki !

This has gone a bit tricky, the problem is not with using named range. The problem is with comparison of two array with different dimensions using comparitive operators.
Just see the attached file (Sheet 1)

In column A I had entered Start Date/Time. In Column B I had entered End Date/Time.
In column C I had calculated total time in minutes excluding weekends (Friday & Saturday), any non-official time & holidays using below formula.

=(((SUMPRODUCT(IF((WEEKDAY(ROW(INDIRECT(INT(A2)&":"&INT(B2))),1)=6)+(WEEKDAY(ROW(INDIRECT(INT(A2)&":"&INT(B2))),1)=7),0,1))-SUMPRODUCT(IF(IFERROR(MATCH(ROW(INDIRECT(INT(A2)&":"&INT(B2))),Holiday,0),0),1,0)))*9)-(IF(INT(B2)=IFERROR(INDEX(Holiday,MATCH(INT(B2),Holiday,0)),0),0,((WEEKDAY(B2,1)<6)*MAX(0,MIN(9,17-MOD(B2,1)*24)))))-(IF(INT(A2)=IFERROR(INDEX(Holiday,MATCH(INT(A2),Holiday,0)),0),0,((WEEKDAY(A2,1)<6)*MAX(0,MIN(9,MOD(A2,1)*24-8))))))*60

(Note: This is an array formula so Ctrl+Shift+Enter is must after entering it. Enter in C2 with CSE and copy down)

My Holiday list is in column E. I had used dynamic named range HOLIDAY. I had use below formula to create it.

=Sheet1!$E$2:INDEX(Sheet1!$E$2:$E$10,COUNTA(Sheet1!$E$2:$E$10))

Just check all the possible cases. I had checked some of them. Advise if any issue.

Regards!
Click to expand...

It's working perfectly thank you very much
 
You must log in or register to reply here.
Back
Top