Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1692
  • Last Modified:

How to make my formula return blank value rather than "0"

Hello,

I am using the following formula to automatically populate a spreadsheet with some default values.

=IF(ISERROR(INDEX(Sheet1!$A$25:$AD$10000,$B4,H$2)),0,INDEX(Sheet1!$A$25:$AD$10000,$B4,H$2))

However, in the above example if the value in Sheet1 is blank i.e an empty cell, excel returns 0/01/1900 as the result.

How can I get excel to return a blank cell rather than 0/01/1900?

Both the source and destination cell are formatted as "Date".
0
vegas86
Asked:
vegas86
  • 2
  • 2
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:

=IF(ISERROR(INDEX(Sheet1!$A$25:$AD$10000,$B4,H$2)),"",INDEX(Sheet1!$A$25:$AD$10000,$B4,H$2))

Kevin
0
 
vegas86Author Commented:
Hi Kevin,

I tried that before I posted my question and again after you suggested it but it is still coming up with 0/01/1900
0
 
barry houdiniCommented:
You could stay with the existing formula and just format the formula cell with this custom format

d/mm/yyyy;;

then zeros display as blank.....or in Excel 2010 you could shorten the formula by using IFERROR, i.e.

=IFERROR(INDEX(Sheet1!$A$25:$AD$10000,$B4,H$2),"")

regards, barry
0
 
zorvek (Kevin Jones)ConsultantCommented:
Then set the format as Barry has suggested.

Kevin
0
 
vegas86Author Commented:
Works perfectly!! thank you so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now