Calculate working hours

  • Calculate working Hours when the shift timing is 10 Pm to 6 am

    •  

    Functions and Operators Used:
  • HOUR Function
  • IF Statement
  • AND Operator

    •  

    Download The Workbook

    Consolidated Formula:

    =IF(AND(HOUR(B3)=22, HOUR(C3)= 6), 8, IF(AND( HOUR(B3)>=22, HOUR(C3)<=6), 24 - (HOUR(B3) - HOUR(C3)),IF(AND(HOUR(B3)>22,HOUR(C3)> 6), 24 - HOUR(B3)+ 6,IF(AND(HOUR(B3)<22,HOUR(C3)< 6,HOUR(B3)>6), 2 + HOUR(C3),IF(AND(HOUR(B3)>=1,HOUR(C3)< 6), HOUR(C3)-HOUR(B3))))))

     

     

     

     

     

Hi Everyone,
Welcome to Excel Functions Tutorial. This Tutorial provides comprehensive knowledge on Excel formulas to the user. If you like this web page kindly Share this to your near and dear. I Prepared this content as per my level of knowledge, if any changes required kindly bring to my notice. Thanks for visiting this web page

Thanks,
Pavan Kumar Gundlapalli