Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
This date 20071017072917 into this type of date 10/17/2007 29:17
How to convert This date 20071017072917 into this type of date 10/17/2007 29:17
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
This date 20071017072917 into this type of date 10/17/2007 29:17
One way (assuming you really wanted 10/17/2007 07:29:17 rather than what
you posted): =--TEXT(A1,"0000\/00\/00 00\:00\:00") Format as mm/dd/yyyy hh:mm:ss t In article , Micro wrote: How to convert This date 20071017072917 into this type of date 10/17/2007 29:17 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
This date 20071017072917 into this type of date 10/17/2007 29:17
On Wed, 17 Oct 2007 09:38:04 -0700, Micro
wrote: How to convert This date 20071017072917 into this type of date 10/17/2007 29:17 The formula: =TEXT(A1,"0000\/00\/00 00\:00\:00") will convert it to a date representation Excel can understand, but it will be different than what you request: 2007/10/17 07:29:17 Also, this is a text string. To convert this to a Excel date/time value, and then display it without showing the hours, you could: C1: =--B1 (assumes above formula is in B1) or B1: =--TEXT(A1,"0000\/00\/00 00\:00\:00") Format/Cells/Number/Custom Type: mm/dd/yyyy mm:ss OR you could use the formula: =TEXT(TEXT(A1,"0000\/00\/00 00\:00\:00"),"mm/dd/yy mm:ss") If you really want the hours to be displayed, and your request excluding them was a typo, merely add hh: prior to the mm:ss =TEXT(TEXT(A1,"0000\/00\/00 00\:00\:00"),"mm/dd/yy hh:mm:ss") --ron |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
This date 20071017072917 into this type of date 10/17/2007 29:17
Shouldn't it be 10/17/2007 07:29:17?
If so you can use =TEXT(LEFT(A1,8),"0000-00-00")+TEXT(RIGHT(A1,6),"00\:00\:00") that will give you 39372.3120023148 which is the serial date and the decimal time value so then you need to format the result as mm/dd/yyyy hh:mm:ss which will return 10/17/2007 07:29:17 -- Regards, Peo Sjoblom "Micro" wrote in message ... How to convert This date 20071017072917 into this type of date 10/17/2007 29:17 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to convert date type to text type | Excel Discussion (Misc queries) | |||
I type in 1-2, how can I keep that from defaulting to a date? | Excel Discussion (Misc queries) | |||
Problems with DATE data type | New Users to Excel | |||
How do you add one date to another? I type in 1/31/05 in A1 and I. | Excel Worksheet Functions | |||
Why can't I type in a date? | Excel Discussion (Misc queries) |