Date issue-displaying as 0001-01-01
 

I have imported a file that is system generated from our IBM server, so can't
change it on that side. The records that don't have a date come across as
0001-01-01. I need to know if there is a way to format these cells to
display as blanks if it contains this. Hope someone can help....have
searched and searched and haven't been successful in finding anything?

Date issue-displaying as 0001-01-01
 

ok, got the formula below to work, is that right, or is there a way to
shorten it?

=IF(VLOOKUP($A3,'census file'!$B$2:$F$572,5,FALSE)="0001-01-01","
",VLOOKUP($A3,'census file'!$B$2:$F$572,5,FALSE))

"Tasha" wrote:

I have imported a file that is system generated from our IBM server, so can't
change it on that side. The records that don't have a date come across as
0001-01-01. I need to know if there is a way to format these cells to
display as blanks if it contains this. Hope someone can help....have
searched and searched and haven't been successful in finding anything?

Date issue-displaying as 0001-01-01
 

Looks good to me. Essentially, you are looking up the value in $A3 on your
census file tab table $B$2:$F$572, and if the 5th column of that table for
the $A3 reference is "0001-01-01" then show nothing, otherwise display what
is there.
--
** John C **


"Tasha" wrote:

ok, got the formula below to work, is that right, or is there a way to
shorten it?

=IF(VLOOKUP($A3,'census file'!$B$2:$F$572,5,FALSE)="0001-01-01","
",VLOOKUP($A3,'census file'!$B$2:$F$572,5,FALSE))

"Tasha" wrote:

I have imported a file that is system generated from our IBM server, so can't
change it on that side. The records that don't have a date come across as
0001-01-01. I need to know if there is a way to format these cells to
display as blanks if it contains this. Hope someone can help....have
searched and searched and haven't been successful in finding anything?

Date issue-displaying as 0001-01-01
 

Ok :) thanks John!!!

"John C" wrote:

Looks good to me. Essentially, you are looking up the value in $A3 on your
census file tab table $B$2:$F$572, and if the 5th column of that table for
the $A3 reference is "0001-01-01" then show nothing, otherwise display what
is there.
--
** John C **


"Tasha" wrote:

ok, got the formula below to work, is that right, or is there a way to
shorten it?

=IF(VLOOKUP($A3,'census file'!$B$2:$F$572,5,FALSE)="0001-01-01","
",VLOOKUP($A3,'census file'!$B$2:$F$572,5,FALSE))

"Tasha" wrote:

I have imported a file that is system generated from our IBM server, so can't
change it on that side. The records that don't have a date come across as
0001-01-01. I need to know if there is a way to format these cells to
display as blanks if it contains this. Hope someone can help....have
searched and searched and haven't been successful in finding anything?

Date issue-displaying as 0001-01-01
 

Your formula broke at a bad spot.

I wouldn't use: ," ",
I'd use: ,"",

No reason to put an extra space character in the cell.

Tasha wrote:

ok, got the formula below to work, is that right, or is there a way to
shorten it?

=IF(VLOOKUP($A3,'census file'!$B$2:$F$572,5,FALSE)="0001-01-01","
",VLOOKUP($A3,'census file'!$B$2:$F$572,5,FALSE))

"Tasha" wrote:

I have imported a file that is system generated from our IBM server, so can't
change it on that side. The records that don't have a date come across as
0001-01-01. I need to know if there is a way to format these cells to
display as blanks if it contains this. Hope someone can help....have
searched and searched and haven't been successful in finding anything?

--

Dave Peterson

Date issue-displaying as 0001-01-01
 

Thanks Dave, I took the extra space out.....

"Dave Peterson" wrote:

Your formula broke at a bad spot.

I wouldn't use: ," ",
I'd use: ,"",

No reason to put an extra space character in the cell.

Tasha wrote:

ok, got the formula below to work, is that right, or is there a way to
shorten it?

=IF(VLOOKUP($A3,'census file'!$B$2:$F$572,5,FALSE)="0001-01-01","
",VLOOKUP($A3,'census file'!$B$2:$F$572,5,FALSE))

"Tasha" wrote:

I have imported a file that is system generated from our IBM server, so can't
change it on that side. The records that don't have a date come across as
0001-01-01. I need to know if there is a way to format these cells to
display as blanks if it contains this. Hope someone can help....have
searched and searched and haven't been successful in finding anything?

--

Dave Peterson