Forum Discussion

Bob_m_m's avatar
Bob_m_m
Copper Contributor
Oct 23, 2022
Solved

Combined Formula

Dear All,  I Need some combined formula to Analyze the Complaint passed 6 Hrs or Not. Actually it is having some exclusions and we need to incorporate that  Exclusion  - Night 10PM to morning 8AM ...
excel
  • OliverScheurich's avatar
    OliverScheurich
    Oct 23, 2022

    Bob_m_m 

    =IF(AND(F3>=TODAY()+"22:00:00",F3<TODAY()+"23:59:59"),TODAY()+1+"08:00:00"+(6/24),IF(F3>TODAY()+"16:00:00",(TODAY()+1+"08:00:00"+("06:00:00"-(TODAY()+"22:00:00"-F3))),IF(AND(F3>=TODAY()+"08:00:00",F3<="16:00:00"),F3+(6/24),(IF(AND(F3<TODAY()+"08:00:00",F3>TODAY()+"00:00:00"),TODAY()+"08:00:00"+(6/24),IF(F3<TODAY()-1+"16:00:00",F3+(6/24),IF(AND(F3>=TODAY()-1+"22:00:00",F3<TODAY()-1+"23:59:59"),TODAY()-1+1+"08:00:00"+(6/24),IF(AND(F3>=(TODAY()-1+"16:00:00"),F3<=(TODAY()-1+"22:00:00")),TODAY()-1+1+"08:00:00"+"06:00:00"-(TODAY()-1+"22:00:00"-F3),NOW()-F3))))))))

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Oct 23, 2022

Bob_m_m 

=IF(AND(F3>=TODAY()+"22:00:00",F3<TODAY()+"23:59:59"),TODAY()+1+"08:00:00"+(6/24),IF(F3>TODAY()+"16:00:00",(TODAY()+1+"08:00:00"+("06:00:00"-(TODAY()+"22:00:00"-F3))),IF(AND(F3>=TODAY()+"08:00:00",F3<="16:00:00"),F3+(6/24),(IF(AND(F3<TODAY()+"08:00:00",F3>TODAY()+"00:00:00"),TODAY()+"08:00:00"+(6/24),IF(F3<TODAY()-1+"16:00:00",F3+(6/24),IF(AND(F3>=TODAY()-1+"22:00:00",F3<TODAY()-1+"23:59:59"),TODAY()-1+1+"08:00:00"+(6/24),IF(AND(F3>=(TODAY()-1+"16:00:00"),F3<=(TODAY()-1+"22:00:00")),TODAY()-1+1+"08:00:00"+"06:00:00"-(TODAY()-1+"22:00:00"-F3),NOW()-F3))))))))

  • Bob_m_m's avatar
    Bob_m_m
    Copper Contributor
    Oct 23, 2022
    It was just amazing , it worked very well . thank you for the quick response and the great support

Resources