| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| confused,please help | Excel Discussion (Misc queries) | |||
| Can I change a date with no format (20051111) to date format? | New Users to Excel | |||
| day/month/year in incorrect format for date format | Excel Worksheet Functions | |||
| How can I convert a date format to an ISO week format (in EXCEL)? | Excel Discussion (Misc queries) | |||
| Excel 2000 date format cannot be set to Australian date format | Excel Discussion (Misc queries) | |||
Hybrid Mode
