# Help to with nesting formulas.

#### mbtaichi

##### Board Regular
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

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### DanteAmor

##### Well-known Member
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

##### Board Regular
That excellent, thank you

Replies
4
Views
323
Replies
3
Views
172
Replies
18
Views
411
Replies
1
Views
186
Replies
1
Views
534

1,171,463
Messages
5,875,660
Members
433,146
Latest member
MinhTrang

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back