K
Krish
I have a text "Dec 12 2003" in a field. How to convert the text to a date
format like 12/12/2003?
format like 12/12/2003?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
N
Norman Harker
Hi Krish!
Try:
=VALUE(MID(A1,5,2)&LEFT(A1,3)&RIGHT(A1,4))
Or:
=--(MID(A1,5,2)&LEFT(A1,3)&RIGHT(A1,4))
Now format mm/dd/yyyy or dd/yy/yyyy depending upon what you think
1/12/2003 means (1-Dec-2003 or 12-Jan-2003)
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Try:
=VALUE(MID(A1,5,2)&LEFT(A1,3)&RIGHT(A1,4))
Or:
=--(MID(A1,5,2)&LEFT(A1,3)&RIGHT(A1,4))
Now format mm/dd/yyyy or dd/yy/yyyy depending upon what you think
1/12/2003 means (1-Dec-2003 or 12-Jan-2003)
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
B
Bernard Liengme
Hi Krish,
1) On Sheet2 in A1 type Jan; select this call and drag fill handle to the
right giving the 3-letter names of the 12 months in A1:L1
2) Assume your first date is in A2 on Sheet1
In B1 (insert a blank column if needed) use
=DATE(RIGHT(A2,4),MATCH(LEFT(A2,3),Sheet2!$A$1:$L$1,0),MID(A2,5,2))
3) Format to taste and copy down the column.
4) Insert a blank column C if required
5) Copy dates in B2:B10000, click on cell C2 and use Edit|Paste Special|
Values and Number format
6) If required, delete columns A and B
As always - make a backup of important data before experimenting.
Best wishes
1) On Sheet2 in A1 type Jan; select this call and drag fill handle to the
right giving the 3-letter names of the 12 months in A1:L1
2) Assume your first date is in A2 on Sheet1
In B1 (insert a blank column if needed) use
=DATE(RIGHT(A2,4),MATCH(LEFT(A2,3),Sheet2!$A$1:$L$1,0),MID(A2,5,2))
3) Format to taste and copy down the column.
4) Insert a blank column C if required
5) Copy dates in B2:B10000, click on cell C2 and use Edit|Paste Special|
Values and Number format
6) If required, delete columns A and B
As always - make a backup of important data before experimenting.
Best wishes
G
Gord Dibben
Krish
Try Data>Text to Columns>Delimited>Next>Next>Column Format>Date>MDY>Finish.
Gord Dibben Excel MVP
Try Data>Text to Columns>Delimited>Next>Next>Column Format>Date>MDY>Finish.
Gord Dibben Excel MVP