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
1	Other Workbook				VLOOKUP	Bad	Good
2	Code	Date1			ZZ	08-Jun-15	08-Jun-15
3	XX	01-Jan-20			XX	01-Jan-20	01-Jan-20
4	SS				WW	17-Dec-16	17-Dec-16
5	WW	17-Dec-16			VV	00-Jan-00
6	ZZ	08-Jun-15			SS	00-Jan-00
7	VV				PP	27-May-12	27-May-12
8	PP	27-May-12			MM	05-Nov-07	05-Nov-07
9	LL				LL	00-Jan-00
10	KK	13-May-09			KK	13-May-09	13-May-09
11	MM	05-Nov-07

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula G2 =VLOOKUP(F2,$B$3:$C$11,2,0) H2 =TEXT(VLOOKUP(F2,$B$3:$C$11,2,0)&"","dd-mmm-yy") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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.