Well, I never thought I'd run into this one! But, a problem came up
at the office with designing a worksheet where difference between two
time values is calculated. The problem was that a user may enter
something that isn't recognized by Excel as a time - for example,
"10am".

I figured I could make these assumptions: the entry will be either
"10am", "10:30am", or something else that will be recognized by Excel
as a time and thus will not need to be interpreted by the formula.
So, here was my first attempt (these are array formulas, use Ctrl+Shift
+Enter):

=IF(ISNUMBER(E14),E14,TIMEVALUE(LEFT(E14,MATCH(1,
(MID(E14,ROW(INDIRECT("1:"&LEN(E14))),
1)<":")*ISERROR(MID(E14,ROW(INDIRECT("1:"&LEN(E14 ))),1)*1),0)-1)&"
"&RIGHT(E14,2)))-IF(ISNUMBER(D14),D14,TIMEVALUE(LEFT(D14,MATCH(1,
(MID(D14,ROW(INDIRECT("1:"&LEN(D14))),
1)<":")*ISERROR(MID(D14,ROW(INDIRECT("1:"&LEN(D14 ))),1)*1),0)-1)&"
"&RIGHT(D14,2)))

Where D14 is Time In, and E14 is Time Out. Of course, this creates 9
levels of function nesting, which won't work in earlier versions of
Excel.

Well, the workaround was to specify a larger array than would ever be
necessary:

=IF(ISNUMBER(E14),E14,TIMEVALUE(LEFT(E14,MATCH(1,( MID(E14,ROW($1:$100),
1)<":")*ISERROR(MID(E14,ROW($1:$100),1)*1),0)-1)&" "&RIGHT(E14,2)))-
IF(ISNUMBER(D14),D14,TIMEVALUE(LEFT(D14,MATCH(1,(M ID(D14,ROW($1:$100),
1)<":")*ISERROR(MID(D14,ROW($1:$100),1)*1),0)-1)&" "&RIGHT(D14,2)))

Just right! So, one more thing to look out for when working with the
new version.