• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1709
  • 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

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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