Vlookup for multiple excel files : dates are in 'number' format

[mathieuv]

Hello,

With some help, I am able to create a Vlookup for multiple excel files.
However, I still have a problem if I work with dates. Instead of having the data in 'data format', excel always give me a 'number format'.

Someone have an idea how I can change it to 'data format'?

Vlookup.xlsx File 1.xlsx File 2.xlsx File 3.xlsx


Many thanks,
Rgds,
Mathieu

[vlady]

try vlookup() *1

[mathieuv]

I have tried:

=IFERROR(VLOOKUP(A5;'[File 1.xlsx]Sheet1'!$A$2:$D$4;3;0)*1;"")&IFERROR(VLOOKUP(A5;'[File 2.xlsx]Sheet1'!$A$2:$D$4;3;0)*1;"")&IFERROR(VLOOKUP(A5;'[File 3.xlsx]Sheet1'!$A$2:$D$4;3;0)*1;"")

=IFERROR(VLOOKUP(A3*1;'[File 1.xlsx]Sheet1'!$A$2:$D$4;3;0);"")&IFERROR(VLOOKUP(A3*1;'[File 2.xlsx]Sheet1'!$A$2:$D$4;3;0);"")&IFERROR(VLOOKUP(A3*1;'[File 3.xlsx]Sheet1'!$A$2:$D$4;3;0);"")

=IFERROR(VLOOKUP(A4;'[File 1.xlsx]Sheet1'!$A$2:$D$4;3;0);"")*1&IFERROR(VLOOKUP(A4;'[File 2.xlsx]Sheet1'!$A$2:$D$4;3;0);"")*1&IFERROR(VLOOKUP(A4;'[File 3.xlsx]Sheet1'!$A$2:$D$4;3;0);"")*1


They give me the same result, data is still in 'number format'.

[JBeaucaire]

Take control of the output.


=YourCurrentVlookupFormulas (that result in getting a date value from one of the sheets).

Now format that number back into a date display of your choosing.

1) Just format the CELL with the date value desired.

or

2) =TEXT(YourCurrentVlookupFormulas, "MM/DD/YYYY")

[mathieuv]

Thanks for your help JBeaucaire, really appreciate it

But I cannot follow your first step.

I have entered following code:
=TEXT(IFERROR(VLOOKUP(A2;'C:\Users\verkenm\Desktop\[File 1.xlsx]Sheet1'!$A$2:$D$4;3;0);"")&IFERROR(VLOOKUP(A2;'C:\Users\verkenm\Desktop\[File 2.xlsx]Sheet1'!$A$2:$D$4;3;0);"")&IFERROR(VLOOKUP(A2;'C:\Users\verkenm\Desktop\[File 3.xlsx]Sheet1'!$A$2:$D$4;3;0);"");"DD/MM/YYYY")

Now it's in a data format, but the year is not visible. You can see it in attachment.

Vlookup.xlsx

[mathieuv]

I have found it, it's JJJJ instead of YYYY.
Many thanks!!

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.