Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#NUM! in Date field calculation
Great site for help!
I have 3 cells. A1 is a date cell (formatted for date mmm/yy) in which I input a start date. A2 is a numeric value representing the length of a project in months. A3 contains this formula =(DATE(YEAR(A1),MONTH(A1)+(A2-1),DAY(A1)) and is formatted as a date cell. It works to a fashion except it returns #NUM! when there is nothing in cell A1 & A2. How do I fix this or get around it? I want the cell A3 to show nothing unless both A1 & A2 have inputs. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#NUM! in Date field calculation
Rick, it is supposed to return #NUM!. When A1 and A2 are blank your
function essentially becomes =DAY(0, 0, 0). When you input values in these two cells, Excel will recalculate and produce a result automatically. If you want to avoid displaying an error value (and display, say, an empty cell instead), use the following formula: =IF(OR(A1=0, A2=0), "", DATE(YEAR(A1),MONTH(A1)+(A2-1),DAY(A1)) HTH Kostis Vezerides |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#NUM! in Date field calculation
One way
=IF(COUNT(A1:A2)<2,"",(DATE(YEAR(A1),MONTH(A1)+(A2-1),DAY(A1)))) -- Regards, Peo Sjoblom "Rick" wrote in message ... Great site for help! I have 3 cells. A1 is a date cell (formatted for date mmm/yy) in which I input a start date. A2 is a numeric value representing the length of a project in months. A3 contains this formula =(DATE(YEAR(A1),MONTH(A1)+(A2-1),DAY(A1)) and is formatted as a date cell. It works to a fashion except it returns #NUM! when there is nothing in cell A1 & A2. How do I fix this or get around it? I want the cell A3 to show nothing unless both A1 & A2 have inputs. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#NUM! in Date field calculation
Thanks for the help...works great!
"vezerid" wrote: Rick, it is supposed to return #NUM!. When A1 and A2 are blank your function essentially becomes =DAY(0, 0, 0). When you input values in these two cells, Excel will recalculate and produce a result automatically. If you want to avoid displaying an error value (and display, say, an empty cell instead), use the following formula: =IF(OR(A1=0, A2=0), "", DATE(YEAR(A1),MONTH(A1)+(A2-1),DAY(A1)) HTH Kostis Vezerides |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#NUM! in Date field calculation
Thanks for your help...it works great, also.
"Peo Sjoblom" wrote: One way =IF(COUNT(A1:A2)<2,"",(DATE(YEAR(A1),MONTH(A1)+(A2-1),DAY(A1)))) -- Regards, Peo Sjoblom "Rick" wrote in message ... Great site for help! I have 3 cells. A1 is a date cell (formatted for date mmm/yy) in which I input a start date. A2 is a numeric value representing the length of a project in months. A3 contains this formula =(DATE(YEAR(A1),MONTH(A1)+(A2-1),DAY(A1)) and is formatted as a date cell. It works to a fashion except it returns #NUM! when there is nothing in cell A1 & A2. How do I fix this or get around it? I want the cell A3 to show nothing unless both A1 & A2 have inputs. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Capturing a date field once only | Excel Worksheet Functions | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
HELP - Date Range In 1 Cell Calculation | Excel Worksheet Functions | |||
date calculation issues | Excel Worksheet Functions | |||
Keeping date format of a field inserted from an excel database | Excel Worksheet Functions |