Blank Date 01/0/1900

[Rahulwork]

hello everyone,

i have a big sheet and data fetch from other workbook via vlookuop

where i have blank cell in base sheet, results are 01/0/1900. i need to fetch month details but bcoz of this it;s showing january which is incorrect. kindly help me to get the month result "0" or blank if we have 01/0/1900 in result

 

[Toadstool]

Hi Rahulwork,

You should fix it in your VLOOKUP when you retrieve the date/not a date.

	B	C	D	E	F	G	H
Code	ZZ
XX	XX
SS	WW
WW	VV
ZZ	SS
VV	PP
PP	MM
LL	LL
KK	KK
MM

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Other Workbook[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]VLOOKUP[/TD]
[TD="align: center"]Bad[/TD]
[TD="align: center"]Good[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]Date1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]08-Jun-15[/TD]
[TD="align: center"]08-Jun-15[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]01-Jan-20[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]01-Jan-20[/TD]
[TD="align: center"]01-Jan-20[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17-Dec-16[/TD]
[TD="align: center"]17-Dec-16[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]17-Dec-16[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]00-Jan-00[/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]08-Jun-15[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]00-Jan-00[/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27-May-12[/TD]
[TD="align: center"]27-May-12[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]27-May-12[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]05-Nov-07[/TD]
[TD="align: center"]05-Nov-07[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]00-Jan-00[/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]13-May-09[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13-May-09[/TD]
[TD="align: center"]13-May-09[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]05-Nov-07[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=VLOOKUP(F2,$B$3:$C$11,2,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2
[/TH]
[TD="align: left"]=TEXT(VLOOKUP(F2,$B$3:$C$11,2,0)&"","dd-mmm-yy")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Hi Rahulwork,

You should fix it in your VLOOKUP when you retrieve the date/not a date.


=TEXT(VLOOKUP(F2,$B$3:$C$11,2,0)&"","dd-mmm-yy")

I wouldn't do it that way as the results are now text values, not date values, which may cause issues with using them for further analysis - eg analysing by month.

I would do it this way.

Excel Workbook
	B	C	D	E	F	G	H
1	Other Workbook				VLOOKUP	Bad	Good
2	Code	Date1			ZZ	8-Jun-15	8-Jun-15
3	XX	1-Jan-20			XX	1-Jan-20	1-Jan-20
4	SS				WW	17-Dec-16	17-Dec-16
5	WW	17-Dec-16			VV	0-Jan-00
6	ZZ	8-Jun-15			SS	0-Jan-00
7	VV				PP	27-May-12	27-May-12
8	PP	27-May-12			MM	5-Nov-07	5-Nov-07
9	LL				LL	0-Jan-00
10	KK	13-May-09			KK	13-May-09	13-May-09
11	MM	5-Nov-07
Blank Date

 

[Rahulwork]

thank you..!

 

[Peter_SSs]

thank you..!

You're welcome.