Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007 backwards compatibility - nesting functions
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Turn off compatibility Checker in Excel 2007? | Excel Discussion (Misc queries) | |||
Color palette compatibility Excel 2007 vs. 2003 | Excel Discussion (Misc queries) | |||
Excel 2007 backward compatibility re colors | New Users to Excel | |||
backwards compatibility for Excel 2007 | Excel Discussion (Misc queries) | |||
Help with nesting functions in Excel | Excel Worksheet Functions |