Forum Discussion

Gerry Poppe's avatar
Gerry Poppe
Copper Contributor
Aug 17, 2017

Short date display in Excel in concatenated filed

I have a cell which contains a serial date which I have formatted as a short date display.  In another field I have text that I want to merge with the cell which contains the short date.  The text field reads "Monthly export data through - " and the second field is a formula (=MAX(E:E)) which determines the most current date.  This cell is formatted as a short date and displays 7/13/2017.  I want my text to read "Monthly export data through - 7/13/2017"   Presently I am getting "Monthly export data through - 42929"  I need to convert the serial number in my text to be the short date 7/13/2017.

 

Any ideas?

excel
Formulas and Functions
    • Gerry Poppe's avatar
      Gerry Poppe
      Copper Contributor
      Aug 17, 2017

      Thanks that worked

       

      ="Monthly Export data collected through - "&TEXT(+J1,"MM/DD/YYYY")

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Aug 17, 2017

        Gerry, exactly. Couple of comments

         

        1) You don't need +J1, use TEXT(J1,...

         

        2) As variant you may format your J1 cell (Ctrl+1, Number, Custom) as

        "Monthly Export data collected through - "dd/mm/yyyy

        You'll have the same text shown in J1, but the cell value will be the date with which you may perform other calculations. 

         

         

Resources