Forum Discussion
Excel one cell formula for calculating Actual SLA End 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.
[.... 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.
mtarler wrote: ``i think @Bob_m_m wanted a formula to calculate the END time not how much time is in between 2 values``
I concur. I totally misread the original posting.
I will delete my response (misdirection). FYI, the "exhaustive" test cases that I provided are at least redundant and perhaps not the right ones for your formulas.
- Its Ok bro. Thank you for your support and time
