| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to transfer "text" into "date"?
Hi,
The dates shown in the report are in text format. As you can see below, it is "DD-MM-YYYY". But as it's is in text format, I can't use it as "date" to do the further analysis. Is there any ways to change the below text into "date"? Thanks. 14-12-1969 26-12-1962 01-06-1960 29-05-1963 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to transfer "text" into "date"?
Select the range. From menu DataText to columnsNextNext will take you to
Step 3 of Wizard. Select Date and select the format (DMY) and hit finish. Excel will convert this to date formats. Try and feedback If this post helps click Yes --------------- Jacob Skaria "lalann" wrote: Hi, The dates shown in the report are in text format. As you can see below, it is "DD-MM-YYYY". But as it's is in text format, I can't use it as "date" to do the further analysis. Is there any ways to change the below text into "date"? Thanks. 14-12-1969 26-12-1962 01-06-1960 29-05-1963 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to transfer "text" into "date"?
Hi,
I assumed your information is in column A, in column B enter =DATE(VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2)),VALUE(R IGHT(A1,2))) "lalann" wrote: Hi, The dates shown in the report are in text format. As you can see below, it is "DD-MM-YYYY". But as it's is in text format, I can't use it as "date" to do the further analysis. Is there any ways to change the below text into "date"? Thanks. 14-12-1969 26-12-1962 01-06-1960 29-05-1963 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to transfer "text" into "date"?
Hi Jacob,
I am using Excel 2003. So I don't have this funtion. May I ask if there is any other way to do it? Thanks. "Jacob Skaria" wrote: Select the range. From menu DataText to columnsNextNext will take you to Step 3 of Wizard. Select Date and select the format (DMY) and hit finish. Excel will convert this to date formats. Try and feedback If this post helps click Yes --------------- Jacob Skaria "lalann" wrote: Hi, The dates shown in the report are in text format. As you can see below, it is "DD-MM-YYYY". But as it's is in text format, I can't use it as "date" to do the further analysis. Is there any ways to change the below text into "date"? Thanks. 14-12-1969 26-12-1962 01-06-1960 29-05-1963 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to transfer "text" into "date"?
Hi Eduardo,
No, it doesn't work. It showed "#VALUE!". "Eduardo" wrote: Hi, I assumed your information is in column A, in column B enter =DATE(VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2)),VALUE(R IGHT(A1,2))) "lalann" wrote: Hi, The dates shown in the report are in text format. As you can see below, it is "DD-MM-YYYY". But as it's is in text format, I can't use it as "date" to do the further analysis. Is there any ways to change the below text into "date"? Thanks. 14-12-1969 26-12-1962 01-06-1960 29-05-1963 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to transfer "text" into "date"?
Excel 2003 does indeed have that function.
-- David Biddulph lalann wrote: Hi Jacob, I am using Excel 2003. So I don't have this funtion. May I ask if there is any other way to do it? Thanks. "Jacob Skaria" wrote: Select the range. From menu DataText to columnsNextNext will take you to Step 3 of Wizard. Select Date and select the format (DMY) and hit finish. Excel will convert this to date formats. Try and feedback If this post helps click Yes --------------- Jacob Skaria "lalann" wrote: Hi, The dates shown in the report are in text format. As you can see below, it is "DD-MM-YYYY". But as it's is in text format, I can't use it as "date" to do the further analysis. Is there any ways to change the below text into "date"? Thanks. 14-12-1969 26-12-1962 01-06-1960 29-05-1963 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to transfer "text" into "date"?
Instead of =DATE(VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2)),VALUE(R IGHT(A1,2))),
Eduardo may have intended to say =DATE(VALUE(RIGHT(A1,4)),VALUE(MID(A1,4,2)),VALUE( LEFT(A1,2))) -- David Biddulph lalann wrote: Hi Eduardo, No, it doesn't work. It showed "#VALUE!". "Eduardo" wrote: Hi, I assumed your information is in column A, in column B enter =DATE(VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2)),VALUE(R IGHT(A1,2))) "lalann" wrote: Hi, The dates shown in the report are in text format. As you can see below, it is "DD-MM-YYYY". But as it's is in text format, I can't use it as "date" to do the further analysis. Is there any ways to change the below text into "date"? Thanks. 14-12-1969 26-12-1962 01-06-1960 29-05-1963 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to transfer "text" into "date"?
Hi David,
Thanks. It works. I also find Text to columns in menu. (My excel is not in English, so try to find what "Text to columns" is.:p) Thanks all. "David Biddulph" wrote: Instead of =DATE(VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2)),VALUE(R IGHT(A1,2))), Eduardo may have intended to say =DATE(VALUE(RIGHT(A1,4)),VALUE(MID(A1,4,2)),VALUE( LEFT(A1,2))) -- David Biddulph lalann wrote: Hi Eduardo, No, it doesn't work. It showed "#VALUE!". "Eduardo" wrote: Hi, I assumed your information is in column A, in column B enter =DATE(VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2)),VALUE(R IGHT(A1,2))) "lalann" wrote: Hi, The dates shown in the report are in text format. As you can see below, it is "DD-MM-YYYY". But as it's is in text format, I can't use it as "date" to do the further analysis. Is there any ways to change the below text into "date"? Thanks. 14-12-1969 26-12-1962 01-06-1960 29-05-1963 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to transfer "text" into "date"?
.... and in fact Excel doesn't need the VALUE function in this case, so you
can simplify to =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)) -- David Biddulph David Biddulph wrote: Instead of =DATE(VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2)),VALUE(R IGHT(A1,2))), Eduardo may have intended to say =DATE(VALUE(RIGHT(A1,4)),VALUE(MID(A1,4,2)),VALUE( LEFT(A1,2))) lalann wrote: Hi Eduardo, No, it doesn't work. It showed "#VALUE!". "Eduardo" wrote: Hi, I assumed your information is in column A, in column B enter =DATE(VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2)),VALUE(R IGHT(A1,2))) "lalann" wrote: Hi, The dates shown in the report are in text format. As you can see below, it is "DD-MM-YYYY". But as it's is in text format, I can't use it as "date" to do the further analysis. Is there any ways to change the below text into "date"? Thanks. 14-12-1969 26-12-1962 01-06-1960 29-05-1963 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) | |||
Linear Mode
