Blank date column return datel 00-Jan-00

F

Frank Situmorang

Hello:

Following is my formula to be put in data sheet ( destination sheet) while
New Invoice List is the name of my source sheet.

My problem is the blank date in the source sheet is shown as " 0-Jan-00",
while I want it to renturn just as source/ blank.

Please help me how can we make it , because in the summary sheet I want to
make the sum if blank or zero 0, will not be calculated.

=IF(ISNA(VLOOKUP(H6674,'[NEW INVOICE LIST.xlsb]INVOICE
''08'!$A$7:$AX$6000,32,FALSE)),IF(ISNA(VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''07'!$A$7:$AX$6000,31,FALSE)),IF(ISNA(VLOOKUP(H6674,'[NEW
INVOICE LIST.xlsb]INVOICE
''06'!$A$7:$AX$6000,31,FALSE)),IF(ISNA(VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''05'!$A$7:$AX$6000,29,FALSE)),VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''05'!$A$7:$AX$6000,29,FALSE)),VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''06'!$A$7:$AX$6000,31,FALSE)),VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''07'!$A$7:$AX$6000,31,FALSE)),VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''08'!$A$7:$AX$6000,32,FALSE))
 
M

M Kan

You could add another IF statement that says if the result = 0, then return
"" otherwise just execute the formula you just laid out.
 
F

Frank Situmorang

Ok Kan, now if I want to total all with the income date with the following
formula, does exel interpret it as 0 or zero??:

=SUM(IF(DATA!$F$11:$F$9098=A7,IF(DATA!$X$11:$X$9098<>0,IF(DATA!$X$11:$X$9098<>"cancell",DATA!$AB$11:$AB$9098,0),0),0))

This range is the date column, which shows "0-Jan-00"

Thanks for your help
--
H. Frank Situmorang


M Kan said:
You could add another IF statement that says if the result = 0, then return
"" otherwise just execute the formula you just laid out.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


Frank Situmorang said:
Hello:

Following is my formula to be put in data sheet ( destination sheet) while
New Invoice List is the name of my source sheet.

My problem is the blank date in the source sheet is shown as " 0-Jan-00",
while I want it to renturn just as source/ blank.

Please help me how can we make it , because in the summary sheet I want to
make the sum if blank or zero 0, will not be calculated.

=IF(ISNA(VLOOKUP(H6674,'[NEW INVOICE LIST.xlsb]INVOICE
''08'!$A$7:$AX$6000,32,FALSE)),IF(ISNA(VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''07'!$A$7:$AX$6000,31,FALSE)),IF(ISNA(VLOOKUP(H6674,'[NEW
INVOICE LIST.xlsb]INVOICE
''06'!$A$7:$AX$6000,31,FALSE)),IF(ISNA(VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''05'!$A$7:$AX$6000,29,FALSE)),VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''05'!$A$7:$AX$6000,29,FALSE)),VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''06'!$A$7:$AX$6000,31,FALSE)),VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''07'!$A$7:$AX$6000,31,FALSE)),VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''08'!$A$7:$AX$6000,32,FALSE))
 
T

T. Valko

Use another cell!

Use that current formula and "hide" it somewhere. Then use another formula
that tests that cell for 0.

Your long formula in cell A1.

Then test that cell:

=IF(A1=0,"",A1)

--
Biff
Microsoft Excel MVP


M Kan said:
You could add another IF statement that says if the result = 0, then
return
"" otherwise just execute the formula you just laid out.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


Frank Situmorang said:
Hello:

Following is my formula to be put in data sheet ( destination sheet)
while
New Invoice List is the name of my source sheet.

My problem is the blank date in the source sheet is shown as " 0-Jan-00",
while I want it to renturn just as source/ blank.

Please help me how can we make it , because in the summary sheet I want
to
make the sum if blank or zero 0, will not be calculated.

=IF(ISNA(VLOOKUP(H6674,'[NEW INVOICE LIST.xlsb]INVOICE
''08'!$A$7:$AX$6000,32,FALSE)),IF(ISNA(VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE
''07'!$A$7:$AX$6000,31,FALSE)),IF(ISNA(VLOOKUP(H6674,'[NEW
INVOICE LIST.xlsb]INVOICE
''06'!$A$7:$AX$6000,31,FALSE)),IF(ISNA(VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''05'!$A$7:$AX$6000,29,FALSE)),VLOOKUP(H6674,'[NEW
INVOICE
LIST.xlsb]INVOICE ''05'!$A$7:$AX$6000,29,FALSE)),VLOOKUP(H6674,'[NEW
INVOICE
LIST.xlsb]INVOICE ''06'!$A$7:$AX$6000,31,FALSE)),VLOOKUP(H6674,'[NEW
INVOICE
LIST.xlsb]INVOICE ''07'!$A$7:$AX$6000,31,FALSE)),VLOOKUP(H6674,'[NEW
INVOICE
LIST.xlsb]INVOICE ''08'!$A$7:$AX$6000,32,FALSE))
 
F

Frank Situmorang

Thanks Valko, there is another way to do it, but since I do not want to
insert column anymore, I leave it as it is.

Thanks you very much

Frank
--
H. Frank Situmorang


T. Valko said:
Use another cell!

Use that current formula and "hide" it somewhere. Then use another formula
that tests that cell for 0.

Your long formula in cell A1.

Then test that cell:

=IF(A1=0,"",A1)

--
Biff
Microsoft Excel MVP


M Kan said:
You could add another IF statement that says if the result = 0, then
return
"" otherwise just execute the formula you just laid out.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


Frank Situmorang said:
Hello:

Following is my formula to be put in data sheet ( destination sheet)
while
New Invoice List is the name of my source sheet.

My problem is the blank date in the source sheet is shown as " 0-Jan-00",
while I want it to renturn just as source/ blank.

Please help me how can we make it , because in the summary sheet I want
to
make the sum if blank or zero 0, will not be calculated.

=IF(ISNA(VLOOKUP(H6674,'[NEW INVOICE LIST.xlsb]INVOICE
''08'!$A$7:$AX$6000,32,FALSE)),IF(ISNA(VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE
''07'!$A$7:$AX$6000,31,FALSE)),IF(ISNA(VLOOKUP(H6674,'[NEW
INVOICE LIST.xlsb]INVOICE
''06'!$A$7:$AX$6000,31,FALSE)),IF(ISNA(VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''05'!$A$7:$AX$6000,29,FALSE)),VLOOKUP(H6674,'[NEW
INVOICE
LIST.xlsb]INVOICE ''05'!$A$7:$AX$6000,29,FALSE)),VLOOKUP(H6674,'[NEW
INVOICE
LIST.xlsb]INVOICE ''06'!$A$7:$AX$6000,31,FALSE)),VLOOKUP(H6674,'[NEW
INVOICE
LIST.xlsb]INVOICE ''07'!$A$7:$AX$6000,31,FALSE)),VLOOKUP(H6674,'[NEW
INVOICE
LIST.xlsb]INVOICE ''08'!$A$7:$AX$6000,32,FALSE))
 

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