View original (French)

Excel merge 3 cells in date format

didinemaurice -  
 jj -
Hello,

I would like to merge several cells containing numbers into one to form a date:
for example, A1 = 18, B1 = 08, C1 = 1969 to produce D1 = 18/08/1969
please note that I need to keep the leading 0s in front of the numbers and the separator /.
I tried with CONCATENATE(A1,"/",B1,"/",C1), but the result does not keep the 0s.

Thank you in advance for your help
Adeline

3 answers

  1.
    g Posted messages 1285 Status Member 578
     
    Hello,

    =(CONCATENATE(A1,"/",&B1,"/",&C1))*1
    in date format *14/03/2001
    or
    =(A1&"/"&B1&"/"&C1)*1
    in date format *14/03/2001

    Best regards.
    4
    1. didinemaurice
       
      Hello,

      thank you for your response but it doesn't work :(
      do you know a way to transform a cell in this form 19010818 into 08/18/1901?
      thank you
      0
    2. g Posted messages 1285 Status Member 578
       
      Sorry, no other solution.
      As far as I'm concerned (Excel 2003), it works perfectly.
      0
    3. jj
       
      Thank you for your valuable help.
      0
  2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Re,

    And why not ask the right question directly???

    =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
    You will get a number like 25433, set the desired date format as explained earlier.

    eric
    2
    1. didinemaurice
       
      thank you
      0
  3. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    You need to create a date in Excel format if you want to work with it properly later.
    =DATE(C1;B1;A1)

    Then right-click on the cell, 'format cells... / number / date' and there you choose the display format you want. This only changes the display; the value remains the same.
    You can also use custom formats like:
    ddd dd/mm/yy
    or
    mmmm yy
    etc

    In custom format you put: dd/mm/yyyy

    eric
    1
    1. didinemaurice
       
      Hello Eric,
      with this formula, I'm not keeping the actual data from the cells, it creates an unknown date for me..
      do you have a way to convert a date in YYYYMMDD format (which is in a single cell) into DD/MM/YYYY knowing that the date cell format doesn't work.
      thank you
      0