Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
end result
=DATEDIF(C9,$F$1,"y") formula is in cell D9
=IF(D9=$H$6,4,IF(D9=$H$5,3,IF(D9=$H$4,2,IF(D9= $H$3,1,IF(D9<$H$3,0))))) formula in cell E9 The above formulas work fine as long as there is a date in cell C9. If the cell C9 is blank I get a return of 107 in column D9 and 4 in column E9 which I assume is correct. The solution I am looking for is: When C9 is blank I want D9 to = 0 (zero) also making E9 = 0(zero). I though making all the cells in C =F1 but column C is an input cell which removes the formula making the cell blank if the entry is removed at a later date. In short, is it possible to make a cell point to F1 as the default value if it is blank? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
end result
Hi!
Try this: =IF(C9="",0,DATEDIF(C9,$F$1,"y")) Biff "macamarr" wrote in message ... =DATEDIF(C9,$F$1,"y") formula is in cell D9 =IF(D9=$H$6,4,IF(D9=$H$5,3,IF(D9=$H$4,2,IF(D9= $H$3,1,IF(D9<$H$3,0))))) formula in cell E9 The above formulas work fine as long as there is a date in cell C9. If the cell C9 is blank I get a return of 107 in column D9 and 4 in column E9 which I assume is correct. The solution I am looking for is: When C9 is blank I want D9 to = 0 (zero) also making E9 = 0(zero). I though making all the cells in C =F1 but column C is an input cell which removes the formula making the cell blank if the entry is removed at a later date. In short, is it possible to make a cell point to F1 as the default value if it is blank? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
end result
Works great Thanks!
"Biff" wrote: Hi! Try this: =IF(C9="",0,DATEDIF(C9,$F$1,"y")) Biff "macamarr" wrote in message ... =DATEDIF(C9,$F$1,"y") formula is in cell D9 =IF(D9=$H$6,4,IF(D9=$H$5,3,IF(D9=$H$4,2,IF(D9= $H$3,1,IF(D9<$H$3,0))))) formula in cell E9 The above formulas work fine as long as there is a date in cell C9. If the cell C9 is blank I get a return of 107 in column D9 and 4 in column E9 which I assume is correct. The solution I am looking for is: When C9 is blank I want D9 to = 0 (zero) also making E9 = 0(zero). I though making all the cells in C =F1 but column C is an input cell which removes the formula making the cell blank if the entry is removed at a later date. In short, is it possible to make a cell point to F1 as the default value if it is blank? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
end result
You're welcome!
Biff "macamarr" wrote in message ... Works great Thanks! "Biff" wrote: Hi! Try this: =IF(C9="",0,DATEDIF(C9,$F$1,"y")) Biff "macamarr" wrote in message ... =DATEDIF(C9,$F$1,"y") formula is in cell D9 =IF(D9=$H$6,4,IF(D9=$H$5,3,IF(D9=$H$4,2,IF(D9= $H$3,1,IF(D9<$H$3,0))))) formula in cell E9 The above formulas work fine as long as there is a date in cell C9. If the cell C9 is blank I get a return of 107 in column D9 and 4 in column E9 which I assume is correct. The solution I am looking for is: When C9 is blank I want D9 to = 0 (zero) also making E9 = 0(zero). I though making all the cells in C =F1 but column C is an input cell which removes the formula making the cell blank if the entry is removed at a later date. In short, is it possible to make a cell point to F1 as the default value if it is blank? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
end result
Maybe one of:
=IF(ISNUMBER(C9),DATEDIF(C9,$F$1,"y"),0) =DATEDIF(IF(ISNUMBER(C9),C9,$F$1),$F$1,"y") macamarr wrote: =DATEDIF(C9,$F$1,"y") formula is in cell D9 =IF(D9=$H$6,4,IF(D9=$H$5,3,IF(D9=$H$4,2,IF(D9= $H$3,1,IF(D9<$H$3,0))))) formula in cell E9 The above formulas work fine as long as there is a date in cell C9. If the cell C9 is blank I get a return of 107 in column D9 and 4 in column E9 which I assume is correct. The solution I am looking for is: When C9 is blank I want D9 to = 0 (zero) also making E9 = 0(zero). I though making all the cells in C =F1 but column C is an input cell which removes the formula making the cell blank if the entry is removed at a later date. In short, is it possible to make a cell point to F1 as the default value if it is blank? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number format | Excel Discussion (Misc queries) | |||
Convert number into words | Excel Discussion (Misc queries) | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
EXCEL:NUMBER TO GREEK WORDS | Excel Worksheet Functions | |||
How can i change this VBA project According to Indian Numeric | Excel Discussion (Misc queries) |