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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007 backwards compatibility - nesting functions
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007 backwards compatibility - nesting functions
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 "ilia" wrote in message ups.com... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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",""})) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007 backwards compatibility - nesting functions
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. On Jun 23, 8:04 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007 backwards compatibility - nesting functions
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007 backwards compatibility - nesting functions
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 "ilia" wrote in message oups.com... 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 |
Reply |
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 |