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))
Click to expand...
Click to expand...
 
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))
Click to expand...
Click to expand...
 
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))
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

This formula can not work in Excel 2007 6
To return a blank cell rather than 0% in a vlookup. 9
(VLOOKUP) If cell is blank, use value from next cell? 6
Modifying a complex date formual to count months 4
IF ARRAY with multiple conditions. 1
COLUMN() problem with array formula 0
IF statement with 2 logicals 1
VLOOKUP Errors with Date formats 1

Top