Help to with nesting formulas.

[mbtaichi]

Hi,


I have two formulas that I would like to combine into one if it’s possible.


Formula 1.


=IF('ROTA 1'!$C3="LAPSED","",RIGHT(IF(ISNUMBER(SEARCH("RTO",'ROTA 1'!E3)),"",'ROTA 1'!E3),9))


If cell E3 = (*)0655-1425 it will return 0655-1425


If cell E3 contains RTO it will gave a blank return.


If cell C3 = lapsed it will return blank.


This formula works in my file and I would like to combine it with the following formula which is reading the result of the first formula.


Formula 2.


=IFERROR(SUM(CONCATENATE(MID(C3,6,2),":",RIGHT(C3,2))-(CONCATENATE(LEFT(C3,2),":",MID(C3,3,2)))),"")


If formula one returns 0655-1425, Formula 2 is converting it into time and subtracting the later time from the earlier time to give a result of 7:30.


This formula also works in my file, but I’d like to be able to tidy it up a little by combining both into one formula.


Unfortunately, I’m unable to format cell E3 as time as there are other formulas reading this cell as well and won’t work if cell E3 is formatted as time.


Thank you for any help given.


Mark

 

[DanteAmor]

Try this

=IF(OR('ROTA 1'!$C3="LAPSED",ISNUMBER(SEARCH("RTO",'ROTA 1'!E3))),"",(MID(RIGHT('ROTA 1'!E3,9),6,2)&":"&RIGHT(RIGHT('ROTA 1'!E3,9),2))-(LEFT(RIGHT('ROTA 1'!E3,9),2)&":"&MID(RIGHT('ROTA 1'!E3,9),3,2)))

 

[mbtaichi]

That excellent, thank you

 

[DanteAmor]

Im glad to help you. Thanks for the feedback.