Forum Discussion
Solved
Excel one cell formula for calculating Actual SLA End date & time
Criteria
22:00 - 08:00 - Freezing time
SLA - 6 Hrs from Registered Date & Time
JoeUser2004 i think Bob_m_m wanted a formula to calculate the END time not how much time is in between 2 values. Here is my formula (that works on older excel):
=IF(E6+6/24<=INT(E6)+$D$3,MAX(E6,INT(E6)+$E$3)+6/24,$E$3+1+6/24-$D$3+MIN(E6,INT(E6)+$D$3))and using the newer LET:
=LET(in,E6, delay,6/24, dEnd,$D$3, dStart,$E$3, d,INT(in), h,in-d, freeze,1-dEnd+dStart, IF(h+delay<=dEnd,MAX(in,d+dStart)+delay, freeze+delay+MIN(in,d+dEnd)))this version is a little easier if you need to tweak values and hopefully a little easier to understand/see what is being done.
Hi Team,
Can aby one help to resolve the below
2.46 AM - 17:44 PM - Freezing time
SLA - 4 Hrs from Registered Date & Time
[.... deleted; irrelevant and a misdirection ....]
JoeUser2004 i think Bob_m_m wanted a formula to calculate the END time not how much time is in between 2 values. Here is my formula (that works on older excel):
=IF(E6+6/24<=INT(E6)+$D$3,MAX(E6,INT(E6)+$E$3)+6/24,$E$3+1+6/24-$D$3+MIN(E6,INT(E6)+$D$3))and using the newer LET:
=LET(in,E6, delay,6/24, dEnd,$D$3, dStart,$E$3, d,INT(in), h,in-d, freeze,1-dEnd+dStart, IF(h+delay<=dEnd,MAX(in,d+dStart)+delay, freeze+delay+MIN(in,d+dEnd)))this version is a little easier if you need to tweak values and hopefully a little easier to understand/see what is being done.
- Superb working formula. Thank you very much for your support and time. Have a nice day.
- it is 6hrs excluding night excluding criteria from night 10PM to morning 8AM
