#Value! error in formula

[inddon]

Hello there,

I have 4 columns with a table:
1. Date1
2. Date2 (reference date1)
3. Inv. Payment terms (alpha numeric)
4. Due Date

In the Due Date column I have the following formula:

=IF(ISBLANK([Date1]),"",[Date1])  + IF(ISNUMBER([Inv. Payment Terms]), [Inv. Payment Terms],0)

I am getting #VALUE! error on the cells where cell 'Date1' is empty.

Attached is the workbook for your reference.

Looking forward for your help

Regards
Don

 

[Swapnil]

You are getting error because you cant add blank ( "") with Zero ... Try to use following

=IFERROR(IF(ISBLANK([Date1]),"",[Date1]) + IF(ISNUMBER([Inv. Payment Terms]), [Inv. Payment Terms],0),"")

 

[Khalid NGO]

Hi Inddon,

If you need not to display #VALUE! Error then try IFERROR:

=IFERROR(IF(ISBLANK([Date1]),"",[Date1]) + IF(ISNUMBER([Inv. Payment Terms]), [Inv. Payment Terms],0),"")

 

[Khalid NGO]

@Swapnil
You beat me by a second :D

 

[Swapnil]

@Swapnil
You beat me by a second :D

 

[inddon]

You are getting error because you cant add blank ( "") with Zero ... Try to use following

=IFERROR(IF(ISBLANK([Date1]),"",[Date1]) + IF(ISNUMBER([Inv. Payment Terms]), [Inv. Payment Terms],0),"")

Thank you Swapnil

 

[inddon]

@Swapnil
You beat me by a second :D

Thank you Khalid

 

[Somendra Misra]

@inddon,

The formula in your file is an array formula, but you are entering them as non-array formula. I think you should replace you formula with this one:

=IF(ISBLANK([@Date1]),0,[@Date1]) + IF(ISNUMBER([@[Inv. Payment Terms]]),[@[Inv. Payment Terms]],0)

And format the due date cells to dd-mmm-yy;;;

Regards,

 

[inddon]

@inddon,

The formula in your file is an array formula, but you are entering them as non-array formula. I think you should replace you formula with this one:

=IF(ISBLANK([@Date1]),0,[@Date1]) + IF(ISNUMBER([@[Inv. Payment Terms]]),[@[Inv. Payment Terms]],0)

And format the due date cells to dd-mmm-yy;;;

Regards,

Hello Somendra,

WOW!!!. I learned something new from you today, thank you!.

When I applied your formula it gave me the Due Date result as 01-Jan-1900, when the Date1 column was empty. After I applied ;;; after yyyy, the result was as expected.

Could you please let me know the following:

What is the purpose of having the symbol @(in formula) and ;;; (format) symbols?

Regards
Don

 

[Somendra Misra]

Alternate formula:
=IF([@Date1]="","",IF(ISNUMBER([@[Inv. Payment Terms]]),[@Date1]+[@[Inv. Payment Terms]],[@Date1]))

The @ in table nomenclature represent current row data.

The custom format consists of four part +ve;-ve;0;text. So the result is dates which is +ve or 0 so putting dd-mmm-yyy;;; will show dates as defined and rest thing will become invisible (although 0 will be there) .

Regards,