Forum Discussion

simona6's avatar
simona6
Copper Contributor
Jan 07, 2020

How do I split up a cell containing a date?

I have an Excel document with over 4,000 rows in it, and many in one column are dates.

 

I need to split it so I can run a PHP import, so it matches with our database.

 

If I do =left(W6,2), I assumed it would show 27 (from the date), but it doesn't.

 

So how do you do it?  The format of the date is : dd/mm/yyyy.

 

Thanks.

11 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    simona6 

    And one more point - if use Text to Columns in first column will appear something like 13/01/1900. If select and apply General format it returns back to 13.

  • Hello,

    To split the date:
    1. Select all the dates
    2. Click on Text to Column in Data tab
    3. Select Delimited and click Next
    4. In Step 2, check Other. In the Order box, put in /
    5. In Step 3, choose destination cell
    6 click Finish


    Thats all
      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor

        simona6 

        As an alternative:

         

        I opened your workbook and entered =DAY(A2) in B2 and it displayed "13" as expected. Entering LEFT(A2,2), however, results in "28", being the first two digits of the number value for the date 13 April 1977, which is 28228. That's how Excel stores dates.

        You may use =MONTH(cell) and =YEAR(cell) to extract the month and the year.

Resources