-1

Could you please tell me how to convert this type of data to a date in excel ?

Data to be converted: Jan 19 2009 12:00AM

Type: 19/01/2009

Thanks

Sino
  • 34
  • 4
  • So you want to type `19/01/2009` and for it to change to `Jan 19 2009 12:00AM`? – Zac Oct 31 '17 at 13:33
  • the inverse I want to convert Jan 19 2009 12:00AM to 19/01/2009 Thx – Sino Oct 31 '17 at 13:34
  • Is your date always 2 digit long? E.g. `Jan 01 2009 12:00AM` or `Jan 1 2009 12:00AM` – Jerry Oct 31 '17 at 13:37
  • 1
    text to column, fixed with, mdy and skip, finish. –  Oct 31 '17 at 13:42
  • Date are like that: Feb 16 2009 12:00AM Apr 10 2009 12:00AM May 25 2009 12:00AM Jul 3 2009 12:00AM Sep 7 2009 12:00AM Nov 26 2009 12:00AM Dec 25 2009 12:00AM Jan 1 2010 12:00AM – Sino Oct 31 '17 at 13:45
  • Excel doesn't like your date since it likes to see a **comma** between the day & year, and a **space* between the time & AM/PM. So, we can put them back where excel wants them. If cell `A1` contains: `Jan 19 2009 12:00AM` Then copy this formula to the cell where you want the date: `=TEXT(DATEVALUE(REPLACE(LEFT(A1,LEN(A1)-2)&" "&RIGHT(A1,2),FIND(" ",LEFT(A1,LEN(A1)-2)&" "&RIGHT(A1,2),FIND(" ",LEFT(A1,LEN(A1)-2)&" "&RIGHT(A1,2))+1),1,", ")),"dd/mm/yyyy")` And it will display text: `19/01/2009` – ashleedawg Oct 31 '17 at 14:01
  • ...or, If you still want the time attached to it (and then choose whether to display the time, with cell formatting) then use lengthy-but-working formula: `=DATEVALUE(REPLACE(LEFT(D6,LEN(D6)-2)&" "&RIGHT(D6,2),FIND(" ",LEFT(D6,LEN(D6)-2)&" "&RIGHT(D6,2),FIND(" ",LEFT(D6,LEN(D6)-2)&" "&RIGHT(D6,2))+1),1,", "))+TIMEVALUE(REPLACE(LEFT(D6,LEN(D6)-2)&" "&RIGHT(D6,2),FIND(" ",LEFT(D6,LEN(D6)-2)&" "&RIGHT(D6,2),FIND(" ",LEFT(D6,LEN(D6)-2)&" "&RIGHT(D6,2))+1),1,", "))` and then right-click the cell, choose `Format Cells`, and on the `Number` tab choose a date, time, or custom format, as needed. – ashleedawg Oct 31 '17 at 14:02

2 Answers2

0

You haven't specified what version of Excel you're using. Here's how to do it in 2013.

Typing just 19/01/2009 into an Excel cell automatically formats it as 'Date'.

If you right-click on that cell and select 'Format Cells' a box will appear with 'Date' already highlighted.

Select 'Custom' at the bottom of the left-hand list, then paste the following into the 'Type:' box on the right.
dd/mm/yyyy hh:mm AM/PM

EDIT

You can also select just 'Date' rather than custom, which will do what you've asked in the comments.

Community
  • 1
  • 1
Bedders
  • 1
  • 1
0

With text in A1, in B1 enter:

=DATEVALUE(MID(A1,5,3) & MID(A1,1,4) & MID(A1,8,4))

and format as you wish.

enter image description here

Gary's Student
  • 95,722
  • 10
  • 59
  • 99