How do I have a Date format show in a Formula that is looking up that cell?

Question

I have a formula:

="('"&A6&"','"&B6&"','"&C6&"','"&D6&"','"&E6&"'),"

And B6 is a date and formatted as dd/mm/yyyy (have tried all forms though) so the result from my formula is ('Text', 'Date', 'Text', 'Text') etc - but the date will display without the formatting i.e. '41628' instead of 20/12/2013 - does anyone know how to keep the formatting of the cells in a formula result?

Answer 1

You can apply the format manually using the TEXT() function:

="('"&A6&"','"&TEXT(B6,"dd/mm/yyyy")&"','"&C6&"','"&D6&"','"&E6&"'),"