Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 143
Default Special string convertion to date and time

Rather than fixed fields, look for that first blank:

=DATEVALUE(LEFT(A1,FIND(" ",A1)-1))
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 259
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 143
Default Special string convertion to date and time

=DATEVALUE(LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1))
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Time convertion based on condition Maglez Excel Discussion (Misc queries) 7 May 28th 08 02:35 PM
convert string to date & time [email protected] Excel Worksheet Functions 4 October 30th 06 05:26 PM
Date convertion O.C Excel Discussion (Misc queries) 2 April 24th 06 01:45 PM
string convertion to date problem Rolo[_4_] Excel Programming 4 December 15th 05 04:51 PM
Date/Time - String or Number! RzB Excel Programming 4 October 27th 03 09:44 PM


All times are GMT +1. The time now is 06:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright Đ2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"