Date Format When Concatenated With Text

    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?

    Re: Date Format When Concatenated With Text


    Format the return from VLOOKUP using the TEXT() function. For example:


    [bfn]=CONCATENATE("This is a test for date ", TEXT(VLOOKUP(I7, N3:O5, 2, FALSE), "dd-mmm-yyyy"))[/bfn]


    change the format to whatever you want: 'dd/mm/yy' etc.

    Re: Date Format When Concatenated With Text


    Following the guidance kindly given by Grimes0332 my cell code now reads:


    =IF(B4="Application",CONCATENATE("MUST be lodged no later than ",TEXT((VLOOKUP(J2,Register!A:AB,28,0)-6),"dd/mm/yyyy")),"")


    I'd now like to extend it slightly; instead of looking to see if the value in cell B4 is 'Application' I'd like to test B4 to see if it includes the word 'Application'.


    I've tried putting ###s and ***s in front of Application but that's obviously not how to do it. Further guidance would be much appreciated.

    Re: Date Format When Concatenated With Text


    It gets a little more complicated:


    =IF(ISNUMBER(SEARCH("Application", B4)), CONCATENATE("MUST be lodged no later than ", TEXT(VLOOKUP(I4, N3:O5, 2, FALSE), "DD-MMM-YYYY")), "Not Due Yet")


    The "Not due yet" is just to highlight the alternative, you may want to replace that with "".

    Re: Date Format When Concatenated With Text


    Another perfect result! Once again Grimes0332, thank you very much for your guidance. And yes - just a little more complicated than sticking ### or *** in front of Application.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login