Excel 2007 backwards compatibility - nesting functions

[ilia]

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,(MID(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.

 

[Bernie Deitrick]

The problem was that a user may enter
something that isn't recognized by Excel as a time - for example,
"10am".

You could use Data Validation, and require time entry, with a message
explaining what a valid entry looks like.

Bernie

 

[Roger Govier]

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)

 

[Harlan Grove]

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".

....

Excel requires a space between the time and the AM/PM qualifier. If you
allow for arbitrary leading, interior or trailing spaces, try

=LOOKUP(3E6,--SUBSTITUTE(UPPER(x),{"AM","PM",""},{" AM"," PM",""}))

 

[ilia]

This was what I said too, but our Controller expects stupid users who
won't use the spreadsheet unless they can do it their way. It's a
lost cause as far as I'm concerned, but I had to try.

Thanks all for the other suggestions.

 

[ilia]

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

 

[Roger Govier]

Hi

Just wrapping my formula in a MOD() function will deal with times
crossing 24 hours

=MOD(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),1)
--
Regards

Roger Govier


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