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".
vegas86Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
barry houdiniConnect With a Mentor Commented:
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:

=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
 
zorvek (Kevin Jones)ConsultantCommented:
Then set the format as Barry has suggested.

Kevin
0
 
vegas86Author Commented:
Works perfectly!! thank you so much!
0
All Courses

From novice to tech pro — start learning today.