I would appreciate some assistance with date formatting.
I have a worksheet that is effectively a database, each row having data in any of 28 columns. Column AB contains dates, displayed on the worksheet as dd/mm/yyyy
I have a cover sheet (a worksheet) that attaches to various types of submissions. Range("B4") describes the type of document that is being submitted. Range("B15") tests what is in Range("B4"). If it contains anything other than 'Application' then Range("B15") is left blank. If it contains 'Application' then a vlookup formula is used to find what date is in column AB of the database. This date is then concatenated with text. The relevant formula is:
=IF(B4="Application",CONCATENATE("MUST be lodged no later than ",(VLOOKUP(J2,Register!A:AB,28,0)-7)),"").
This provides for lodgment of the application a week prior to the date contained in column AB of the database worksheet.
The problem I have is that the date is displayed in its default serial format, concatenated with the text - 'MUST be lodged no later than 42540'.
How do I make the date display as dd/mm/yyyy when it's concatenated with text?