confused about date format

G

Guest

I used vlookup to show date in another sheet. in the original sheet it the
date is blank, but in the other sheet as a result of the vlookup, the date is
"00-Jan_00" for the blank date in the original sheet.

My question is how can I show it as original sheet showing it blank (not
00-Jan-00)

Many thanks for any idea

Frank
 
R

Ron Rosenfeld

I used vlookup to show date in another sheet. in the original sheet it the
date is blank, but in the other sheet as a result of the vlookup, the date is
"00-Jan_00" for the blank date in the original sheet.

My question is how can I show it as original sheet showing it blank (not
00-Jan-00)

Many thanks for any idea

Frank

=IF(your_formula=0,"",your_formula)


--ron
 
G

Guest

Ron: It is not that simple for more details let me exlain:

I have 3 sheets: Inv07. Inv06, Inv05
Each sheet contain the same column of having invoice no, amount, date of
payment.
On another workbook, we need to know unpaid invoice in one sheet using
IF(Isna(Vlookup........)showing column
Inov. No, amount, Date of payment.
If date of payment is blank in any other sheets of the orginal workbook,
just show it blank. What I got is blank in original sheet, but it shows
"0-Jan-00" in the summary sheet.

Maybe the problem is more clear, sorry, for us in Indonesia,it is hard to
explain it in English

many thanks

Frank
 
R

Ron Rosenfeld

Ron: It is not that simple for more details let me exlain:

I have 3 sheets: Inv07. Inv06, Inv05
Each sheet contain the same column of having invoice no, amount, date of
payment.
On another workbook, we need to know unpaid invoice in one sheet using
IF(Isna(Vlookup........)showing column
Inov. No, amount, Date of payment.
If date of payment is blank in any other sheets of the orginal workbook,
just show it blank. What I got is blank in original sheet, but it shows
"0-Jan-00" in the summary sheet.

Maybe the problem is more clear, sorry, for us in Indonesia,it is hard to
explain it in English

many thanks

Frank

Frank,

Is there a formula in the cell on the Summary sheet?

If so, what is that formula?

If not, how does the value get to the Summary sheet?


--ron
 
R

Ron Rosenfeld

Ron: It is not that simple for more details let me exlain:

I have 3 sheets: Inv07. Inv06, Inv05
Each sheet contain the same column of having invoice no, amount, date of
payment.
On another workbook, we need to know unpaid invoice in one sheet using
IF(Isna(Vlookup........)showing column
Inov. No, amount, Date of payment.
If date of payment is blank in any other sheets of the orginal workbook,
just show it blank. What I got is blank in original sheet, but it shows
"0-Jan-00" in the summary sheet.

Maybe the problem is more clear, sorry, for us in Indonesia,it is hard to
explain it in English

many thanks

Frank

Another option, instead of the formula option, might be to use Conditional
Formatting to make the value "invisible".

Select the cells which might have 1/0/1900.

Format/Conditional Formatting
Cell Value Is Equal To: 0
Format font to the same color as the background color (nominally white,
but you might have a different background color).


--ron
 
G

Guest

Ron:

In summary sheet, maybe I misnamed it, maybe I should have called it "just
another sheet for another presentation of data in which the date field for
each job number and sales amount number is presented.

This is my formula in my the date field of "just another sheet". In fact in
inv07, inv06, inv05 sheets the date is really blank for the empty date, but
how come it shows "0-Jan-00' in "just another sheet". Is that because of the
formula?
=IF(ISNA(VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''07'!$A$7:$AJ$2500,31,FALSE)),IF(ISNA(VLOOKUP(H122,'C:\hfsfile\DEV\[NEW
INVOICE LIST.xls]INVOICE
''06'!$A$7:$AI$2500,31,FALSE)),VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE
LIST.xls]INVOICE
''05'!$A$6:$AJ$2500,31,FALSE),VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE
LIST.xls]INVOICE
''06'!$A$7:$AI$2500,31,FALSE)),VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE
LIST.xls]INVOICE ''07'!$A$7:$AJ$2500,31,FALSE))

Thanks a lot.

Frank
 
R

Ron Rosenfeld

Ron:

In summary sheet, maybe I misnamed it, maybe I should have called it "just
another sheet for another presentation of data in which the date field for
each job number and sales amount number is presented.

This is my formula in my the date field of "just another sheet". In fact in
inv07, inv06, inv05 sheets the date is really blank for the empty date, but
how come it shows "0-Jan-00' in "just another sheet". Is that because of the
formula?
=IF(ISNA(VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''07'!$A$7:$AJ$2500,31,FALSE)),IF(ISNA(VLOOKUP(H122,'C:\hfsfile\DEV\[NEW
INVOICE LIST.xls]INVOICE
''06'!$A$7:$AI$2500,31,FALSE)),VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE
LIST.xls]INVOICE
''05'!$A$6:$AJ$2500,31,FALSE),VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE
LIST.xls]INVOICE
''06'!$A$7:$AI$2500,31,FALSE)),VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE
LIST.xls]INVOICE ''07'!$A$7:$AJ$2500,31,FALSE))

Thanks a lot.

Frank

Well, as I wrote, you could test your formula and, if it returns a zero value,
substitute a null string.

Or you could use conditional formatting to change the font of a zero to the
background color (nominally white) which would leave it as zero but make it
invisible.

The formula would look like:

=IF((IF(ISNA(VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''07'!$A$7:$AJ$2500,31,FALSE)),
IF(ISNA(VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''06'!$A$7:$AI$2500,31,FALSE)),
VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''05'!$A$6:$AJ$2500,31,FALSE),
VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''06'!$A$7:$AI$2500,31,FALSE)),
VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''07'!$A$7:$AJ$2500,31,FALSE)))=0,"",
IF(ISNA(VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''07'!$A$7:$AJ$2500,31,FALSE)),
IF(ISNA(VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''06'!$A$7:$AI$2500,31,FALSE)),
VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''05'!$A$6:$AJ$2500,31,FALSE),
VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''06'!$A$7:$AI$2500,31,FALSE)),
VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''07'!$A$7:$AJ$2500,31,FALSE)))


--ron
 
G

Guest

Ron:

I am just curious to know why is it showing "0-Jan-00" while in the original
sheet it is blank. Your suggestion of using conditional format is a good
option. Thank you very much.

Frank
 
R

Ron Rosenfeld

Ron:

I am just curious to know why is it showing "0-Jan-00" while in the original
sheet it is blank. Your suggestion of using conditional format is a good
option. Thank you very much.

Frank

Frank,

When you refer to a range reference, you return its "value". The value of a
blank cell is zero. Zero in the date system is the above date.




--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top