Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Special string convertion to date and time
Hi there,
Working with a file and all my dates and times appear as one long text string shown below that I would like to convert to date and time format so that I can manipulate the data anyway I like. Data appears as follows: .. cell A1: 10/1/2011 2:20:00 PM cell A2: 1/18/2011 2:20:00 AM I have tried many different ways and sometimes it works. The problem is when the date and month are not 2 digits. As you see the date or the month can be 1 or 2 digits. How do I convert it so that it works no matter what.. this works only when the month and date are both 2 digits =DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))+RIGHT(A1 ,10) Hope someone can help . Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Special string convertion to date and time
Rather than fixed fields, look for that first blank:
=DATEVALUE(LEFT(A1,FIND(" ",A1)-1)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Special string convertion to date and time
On Feb 1, 8:46*am, James Ravenswood
wrote: Rather than fixed fields, look for that first blank: =DATEVALUE(LEFT(A1,FIND(" ",A1)-1)) Thank you for your time. I tried but it gives me VALUE error |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Special string convertion to date and time
On 2/02/2012 5:00 AM, c stinz wrote:
On Feb 1, 8:46 am, James wrote: Rather than fixed fields, look for that first blank: =DATEVALUE(LEFT(A1,FIND(" ",A1)-1)) Thank you for your time. I tried but it gives me VALUE error Hi Change Cell Format to a custom dd/mm/yy h:mm:ss AM/PM This will give the following result: cell A1: 10/10/2011 2:20:00 PM cell A2: 01/18/2011 2:20:00 AM Unless you want to use 24 hour military time, then dd/mm/yy hh:mm:ss This will give the following result: cell A1: 10/10/2011 14:20:00 cell A2: 01/18/2011 02:20:00 HTH Mick. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Special string convertion to date and time
On Wed, 1 Feb 2012 05:22:10 -0800 (PST), c stinz wrote:
Hi there, Working with a file and all my dates and times appear as one long text string shown below that I would like to convert to date and time format so that I can manipulate the data anyway I like. Data appears as follows: . cell A1: 10/1/2011 2:20:00 PM cell A2: 1/18/2011 2:20:00 AM I have tried many different ways and sometimes it works. The problem is when the date and month are not 2 digits. As you see the date or the month can be 1 or 2 digits. How do I convert it so that it works no matter what.. this works only when the month and date are both 2 digits =DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))+RIGHT(A 1,10) Hope someone can help . Thanks You have also posted this question in microsoft.public.excel where you have several answers. What was the problem with those responses? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Special string convertion to date and time
=DATEVALUE(LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1))
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time convertion based on condition | Excel Discussion (Misc queries) | |||
convert string to date & time | Excel Worksheet Functions | |||
Date convertion | Excel Discussion (Misc queries) | |||
string convertion to date problem | Excel Programming | |||
Date/Time - String or Number! | Excel Programming |