confused about date format
 

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

confused about date format
 

On Mon, 23 Apr 2007 02:54:00 -0700, Frank Situmorang
wrote:

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

confused about date format
 

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


"Ron Rosenfeld" wrote:

On Mon, 23 Apr 2007 02:54:00 -0700, Frank Situmorang
wrote:

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

confused about date format
 

On Mon, 23 Apr 2007 04:20:02 -0700, Frank Situmorang
wrote:

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

confused about date format
 

On Mon, 23 Apr 2007 04:20:02 -0700, Frank Situmorang
wrote:

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

confused about date format
 

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(H12 2,'C:\hfsfile\DEV\[NEW
INVOICE LIST.xls]INVOICE
''06'!$A$7:$AI$2500,31,FALSE)),VLOOKUP(H122,'C:\hf sfile\DEV\[NEW INVOICE
LIST.xls]INVOICE
''05'!$A$6:$AJ$2500,31,FALSE),VLOOKUP(H122,'C:\hfs file\DEV\[NEW INVOICE
LIST.xls]INVOICE
''06'!$A$7:$AI$2500,31,FALSE)),VLOOKUP(H122,'C:\hf sfile\DEV\[NEW INVOICE
LIST.xls]INVOICE ''07'!$A$7:$AJ$2500,31,FALSE))

Thanks a lot.

Frank

"Ron Rosenfeld" wrote:

On Mon, 23 Apr 2007 04:20:02 -0700, Frank Situmorang
wrote:

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

confused about date format
 

On Wed, 25 Apr 2007 00:54:01 -0700, Frank Situmorang
wrote:

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(H1 22,'C:\hfsfile\DEV\[NEW
INVOICE LIST.xls]INVOICE
''06'!$A$7:$AI$2500,31,FALSE)),VLOOKUP(H122,'C:\h fsfile\DEV\[NEW INVOICE
LIST.xls]INVOICE
''05'!$A$6:$AJ$2500,31,FALSE),VLOOKUP(H122,'C:\hf sfile\DEV\[NEW INVOICE
LIST.xls]INVOICE
''06'!$A$7:$AI$2500,31,FALSE)),VLOOKUP(H122,'C:\h fsfile\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

confused about date format
 

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

"Ron Rosenfeld" wrote:

On Mon, 23 Apr 2007 04:20:02 -0700, Frank Situmorang
wrote:

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

confused about date format
 

On Thu, 26 Apr 2007 02:18:05 -0700, Frank Situmorang
wrote:

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