SOLVED

# Combined Formula

Copper Contributor

# 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 clock is freeze (for 1st Day)

Cell F3 - refers to Complaint registered time & Date (dd/mm/yy hh:mm)

My Logic and formula are

 If fault registered yesterday after 4 PM and before 10 PM, yesterday (6hrs - (10:00 PM - reg time )) + next day 08:00AM+bal if yesterday after 10 PM & before 12 Am,next day 08:00AM+6 hrs If yesterday before 4 PM, Reg time + 6 Hrs If today before 8 AM. Today 8:00 AM + 6Hrs If today after 8 Am and before 4 PM , reg time + 6 hrs if today after 4 PM, Today (6hrs - (10:00 PM - reg time )) + next day 08:00AM+bal if today after 10 PM & before 12 Am,next day 08:00AM+6 hrs If not meeting above conditions NOW()-reg time

My individual formula

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

and i required just a single cell formula combining all these logics.

2 Replies
best response confirmed by Bob_m_m (Copper Contributor)
Solution

# Re: Combined Formula

=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))))))))

# Re: Combined Formula

It was just amazing , it worked very well . thank you for the quick response and the great support
1 best response

Accepted Solutions
best response confirmed by Bob_m_m (Copper Contributor)
Solution

# Re: Combined Formula

=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))))))))