Forum Discussion
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
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 allAs 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.
