Forum Discussion

Paul Minichillo's avatar
Paul Minichillo
Copper Contributor
Aug 07, 2017

Trouble converting date while using CONCATENATE function Excel 2007

I have a small spreadsheet that I pull from into another tab (sheet). I have got the CONCATENATE function working except in the date cell. I have tried all the things found wqhen searching and it jus...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 07, 2017

Hi Paul,

 

Dates in Excel are sequential numbers starting from January 01, 1900. Formatting the cell as date you only change the representation. In your formula Excel correctly returns 42957 what is the value for August 10, 2017.

 

Use in formula TEXT(mainsheet!O3,"yyyy-mm-dd")  instead of mainsheet!O3 (or with format you use for dates)

  • Paul Minichillo's avatar
    Paul Minichillo
    Copper Contributor
    Aug 07, 2017

    Sergei,

      Thank you, I have tried that for hours and it wouldnt work. Yours did. Now I have a new question. In my printout sheet when I copy that formula down the column it shows 01-00-1900. Is there a way to stop that from displaying? I want the formula to follow whatever is entered into the cells in the mainsheet but the printout being blank unless there is something in the cell would be nice. I see this happening in other cells also like the w/o column and customer/designer. These, if blank in the mainsheet would be nice to be blank in the printout sheet. I hope this makes sense.

      Thanks again for the help.

      Paul

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Aug 07, 2017
      Paul,

      Simplest way is to check with IF like
      ... & IF(<date> > 1, "-"&TEXT(<date>, format), "") &...

      Forgot you use CONCATENATE, thus above between two commas in it
      • Paul Minichillo's avatar
        Paul Minichillo
        Copper Contributor
        Aug 08, 2017
        Sorry you lost me on this one.

Resources