Excel 2007 backwards compatibility - nesting functions
"ilia" wrote...
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",""}))
|