I can't get this to work for all inputs. For instance, if E14 is
"1:03pm" and D14 is in the AM (e.g. "10:30 am"), the time value will
be negative. Since you don't know whether an input will be a valid
time until the first condition checks out, you can't compare their
values to determine whether you need to add 12 to the latter (may not
even be the desired result, in cases of user entry error). But you
can modify it like this, since it's quite improbable that end user
will enter seconds:

=IF(ISERROR(E12+1),--
(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(E12),"am", ":00 am"),"pm",":00
pm"),".",":")),E12)-IF(ISERROR(D12+1),--
(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(D12),"am", ":00 am"),"pm",":00
pm"),".",":")),D12)

Still only 6 levels of nesting.

Thanks for the idea!

-Ilia


On Jun 23, 1:05 pm, "Roger Govier"
wrote:
Hi

It can be achieved with 2 IF statements, and without an array formula

=IF(ISERROR(E14+1),--(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E14,"am",":00"), "pm"*,":00"),".",":")),E14)
-IF(ISERROR(D14+1),--(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D14,"am",":00"), "pm"*,":00"),".",":")),D14)

--
Regards

Roger Govier