#### Special converting of string date&time to date format

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
 0
stinzc (1)
2/1/2012 2:55:15 AM
excel 39879 articles. 2 followers.

4 Replies
515 Views

Similar Articles

[PageSpeed] 43

hi c stinz,

=DATE(MID(A1,LEN(A1)-FIND(" ",A1)-1,4),MID(A1,FIND("/",A1,FIND("/",A1)+1)-1,1),LEFT(A1,FIND("/",A1)-1))+RIGHT(A1,LEN(A1)-FIND(" ",A1))

put the cell to the desired format

--
isabelle

Le 2012-01-31 21:55, c stinz a �crit :
> 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
 0
isabelle
2/1/2012 4:46:02 AM
In article <e7dcba06-065a-449f-b745-d89902fe0f8f@eb6g2000vbb.googlegroups.com>, c stinz <stinzc@gmail.com> 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(A1,10)
>
>Hope someone can help .  Thanks

Finding the separators (/ - using the find function) and taking the bits
between them will work.

I suspect that, if you turn the "date" part into a number using the value
function, you get a number which you can use (yep, works for me if I type
=value("10/1/2011")  - I get 40553). Format that as a date if you like. :)
Similarly for the time ? (not tested)

HTH :)

 0
2/1/2012 5:03:46 AM
On Tue, 31 Jan 2012 18:55:15 -0800 (PST), c stinz <stinzc@gmail.com> 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(A1,10)
>
>Hope someone can help .  Thanks

If your strings are really in that format, and your Windows Regional Settings are the same  (e.g.: MDY for the date; and ":" for the time separators), then, with the date/time text string in A1:

B1:  =--A1

will convert to an Excel date/time value which can be formatted as a date & time.

If you need to separate the date and the time, then:

Date:
B1: =INT(A1)

Time:
C1: =MOD(A1,1)

Be sure to format B1 and C1 appropriately.
 0
ron6368 (329)
2/1/2012 5:45:46 PM
correction:

y: =MID(A2,FIND("/",A2,FIND("/",A2)+1)+1,4)
m: =LEFT(A2,FIND("/",A2)-1)
d: =MID(A2,FIND("/",A2,FIND("/",A2)+1)-1,1)
h: =RIGHT(A2,LEN(A2)-FIND(" ",A2))

=DATE(y,m,d)+h

=DATE(MID(A2,FIND("/",A2,FIND("/",A2)+1)+1,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,FIND("/",A2,FIND("/",A2)+1)-FIND("/",A2)-1))
+RIGHT(A2,LEN(A2)-FIND(" ",A2))

--
isabelle

Le 2012-01-31 23:46, isabelle a �crit :
> hi c stinz,
>
> =DATE(MID(A1,LEN(A1)-FIND(" ",A1)-1,4),MID(A1,FIND("/",A1,FIND("/",A1)+1)-1,1),
>LEFT(A1,FIND("/",A1)-1))+RIGHT(A1,LEN(A1)-FIND(" ",A1))
>
> put the cell to the desired format
>
>
 0
isabelle
2/1/2012 9:47:34 PM

Similar Artilces:

Convert from RBS to VBA
I have scripts written in reflection basic and I am having to upgrade to Visual Basic for Applications. I'm wondering if there is a simiple way to convert already created scipts from RBS to VBA. Help, I don't want to rewrite them all. I am using version 13 of Reflections programming. -- Grammie You may want to post this message in a Microsoft community that focuses on VBA. This community is for Dynamics GP. -- Charles Allen, MVP "Grammie" wrote: > I have scripts written in reflection basic and I am having to upgrade to > Visual Basic for Applications. I...

Dragon 30-TIMES more accurate than Vista Speech
Dragon NaturallySpeaking (DNS) is *30* *TIMES* more accurate than Windows Speech Recognition (WSR) on any use where unusual words are involved, such as medical usage. Why? - because the "scan documents" feature of WSR is broken, just plain does not work. On a 600 word medical dictation, I get 30 text mistakes when I use WSR; I get ZERO text mistakes using DNS. It takes a long time to find and correct those 30 text mistakes. That is a poor 95% text accuracy, a person would be better off just typing everything in. The same document scanning feature...

Conditional formatting...cont. from 9/25
Rowan, That formula is real close but isn't quite working correctly. I tried it and in my table i put about 10 city abbr. Then in my named range I type "HOU" but for some reason after I did that the cells with "hou" turn red as was supposed to happen but so did the cells with "WAS", and "PIT". If I put in "DAL" into my named range the cells in my table with "DAL" turn red but so do the cells with "WAS", "PIT", and "DEN". So for some reason it is formatting the correct cells but also identifyi...

Formatting numbers in Chinese
Here's a question that arose: I was talking with someone about the numeric formatting problem, and he reminded me about a discussion we'd apparently had over a decade ago, about Chinese numbers. There are 10 symbols for the digits 0,1,2,3,4,5,6,7,8,9 but then ten is represented by something, that as best I recall, looks like a +, so (forgive the Arabic numerals here, but I don't know Chinese anyway...) 11 would be represented by the symbol +1, twelve by +2, through nineteen +9, then 20 would, if I'm remembering correctly, be the characters for 20, up to 100, which would use th...

how do i convert a pub to pdf

Time #4
I am having a problem with calculating time. I can calculate the difference in hours over midnight, but what I can't do is not get a value to appear when I have only put in one number...For instance. I put in the time I start, and in my hours it shows midnight minus those hours without me putting in an ending value for hours worked.. Is there anyway to get this to show an error if they only put in a start time and no quit time? Hopefully this makes sense to someone out there. Any information anyone can give would be helpful. Thanks One way: Say start time is in A1 and qu...

Special Characters
Is there a quick way to convert all special characters to XML format for example '&' to &amp; or '+' sign to ? ? MAF wrote: > Is there a quick way to convert all special characters to XML format for > example '&' to &amp; or '+' sign to ? ? '&' needs to be escaped as &amp; but the '+' sign does not need to be escaped in XML. How you do it depends on the .NET APIs you use, if you use an XmlTextWriter to create XML then the WriteString method for instance does the escaping for you. If you use the DOM th...

Have any soft working on webcam with i420 format
Hi all, Have any software working on webcam with i420 format? "Duy Trinh" <duy.trinh@mobinex.com> wrote in message news:%23zDDAyV6HHA.2752@TK2MSFTNGP06.phx.gbl... > Hi all, > > Have any software working on webcam with i420 format? Most webcams are programmed by using the DirectShow or Video for Windows API's. -- David ...

Can't Copy and Paste or Paste Special between Excel Workbooks
We have a number of Excel users in our office who cannot copy and paste between Excel workbooks. They can copy and paste between worksheets. When you highlight the section to copy and then go to the new workbook both the paste and paste special are "grayed out". This is true whether you right-click the mouse, go to the edit menu, or use control keys. This occurs with any data type and the most simple workbooks. I have seen some suggestions here but none have worked for this particular problem. I have reset the menus and renamed the .xlb files and neither helps. You can open t...

Date Auto Format #2
Hello. Until today, I have always used an excel auto format that seems to have stopped. Prior to today, if I selected a cell and formatted it as a date, I could then type 0826 and hit tab, Excel would then auto format the cell to read 08/26/04. Today, if I type 0826, the auto format changes it to 04/05/02. Please help. Thank you. Hi no way to prevent this. Also are you sure that your first entry really produced thzis outcome. Without VBA this is NOT possible. See: http://www.cpearson.com/excel/DateTimeEntry.htm -- Regards Frank Kabel Frankfurt, Germany Katie wrote: > Hello. ...

Automatic Formatting not Displaying on Shared Calendar
I'm using Outlook 2003 and have used Automatic Formatting to color code appointments. I have given permission to other users to view & edit my calendar. However, they do not see the color codes, although it does appear on mine. How can I make my automatic formatting visible to those who can see my calendar. Thanks, Tanya wrote: > I'm using Outlook 2003 and have used Automatic Formatting to color code > appointments. I have given permission to other users to view & edit my > calendar. However, they do not see the color codes, although it does > appear on mine. I f...

Shortcut to Convert files to 2007
Is there a keyboard shortcut that will convert my files from 2003 to 2007. Currently I am pulling up each individually and clicking Office, Convert, Yes, then close. There are thousands to convert. What about a mass convert function, can you highlight all files from a drive and convert that way? The way that I converted mine was on an iterative basis: I just converted them when I needed to. All I did was to save them as an Excel Workbook and have never run into any problems doing in that way. Please remember to make a back up though just in case things do go wrong / ...

Need help with Time
Hello fellow Excel users. I've researched this group and view PERSONS site on time...but I'm not able to come up with the correct formula. I'm trying to create a project timesheet our tech use in the field. However I can't get the standard time and the prime time formulas to work correctly. My HEADERS A1 = Name - Manually Entered by User B1 = Travel Start Time - Manually Entered by User C1 = Travel Stop Time - Manually Entered by User D1 = Project Start Time =C1 - Locked Cell-Automatically is Travel STOP TIME. E1 = Project Stop Time - Manually Entered by User F1 = Total Proje...

Weird thing happening with dates in 2003
I'm using Excel for a merge database. On sheet 1, I have cells that format dates to be e.g. July 6, 2009. On Sheet 2, I have that cell from Sheet 1 included in a sentence. So... in Sheeet 2, B1="Recorded on "&Sheet1!B29 | Sheet1 B29 being the date I mentioned. I'm getting in my output: "Recorded on 4000" which is really uncool because I'm doing this A LOT in my document. Please help? Do it this way: =3D"Recorded on "&TEXT(Sheet1!B29,"mmmm d, yyyy") Hope this helps. Pete On Oct 16, 3:30=A0pm, Nit_Wit_400 <kflinsp...@gmail...

Converters
Is there a converted for Excel XP 2002 SP 2 to read in a spreadsheet created by an older version (Version 4 of Works for Windows)... If so, how can I get it... Please reply to my email address jpryce85260@yahoo.com ...

Is it possible to add dates in an excel worksheet to Outlook Cale.
I would like to be able to select a cell with a date in & copy the related info, eg: domain name renewal date to Microsoft Outlook Calendar by using a tool within excel, rather than switching between excel & Outlook - is there a function/tool that allows this, eg Smart Tag??? ...

Convert Macro to custom function
Hi I would like to convert this macro to a custom function. Basically what I would like to do is recreate a vlookup in a custom function. I want to say If the cell the vlookup points to is either A, B, C or D then do one thing, else do another. Here is the code which works in a normal macro. I would like a function where I could go =checkit(A1,True Value here, False Value here) Thanks in advance. Chad Sub Checkit() Dim RngCell As Range Dim MyList() As Variant Dim res As Variant Dim Last As Long Dim X As Range Dim ws As Worksheet Last = Range("B&qu...

Format Data Entry
Is there a way to format data so that when entering someone's height as 511 it will come up as 5'11" and when entering 63 it would come up as 6'3" ? Thanks. There may be a way, but I wouldn't do this. Instead, use two cells (feet and inches). Or use one cell and enter total inches. It'll make any further arithmetic much, much easier. Ayuda wrote: > > Is there a way to format data so that when entering someone's height as 511 > it will come up as 5'11" and when entering 63 it would come ...

Specialized ComboBox
Hi. I'm trying to create a combo box, dropdown style, which will enter the text in the edit field to the list box upon hitting enter key, and allow deletion of items from the list box. I'm affraid i have no clue as to how to do this - i thought about deriving from CComboBox and adding handler to keydown. but no idea as to how to exactly. Help will be much appreciated. Ori, You first need to find out if the user has swapped left & right mouse buttons (GetSystemMetrics(SM_SWAPBUTTON)). Then if the user has swapped mouse buttons you need to hook WM_LBUTTONUP, if the user has...

How can I convert an Excel file to an address book?
How can I convert an Excel file to an address book to import into email? ...

Convert from excel to word
I have a workbook, that has multiple worksheets. each worksheet is a form, with identical layout, but different data. Each page is formatted to print A4 fit to page. Is it possible to convert the workbook to a word document, with each worksheet as a seperate page in the word file. Thanks, in anticipation... SS I would suggest this might be better posted in a Word group. You cannot "convert" an Excel workbook to a Word document. AFAIK you would have to copy one sheet at a time into pages of a Word document but someone over in a Word group probably has a method. Or you may...

Caculating Columns Between Certain Dates
I have a spread sheet with for simplicity 4 columns Column 1 is a Date Column, Column 2 is a Company Name, Column 3 is a Payment amount X Column 4 is a Payment amount Y The amount due is the sum of X+Y between the relevant dates. The entries are in date order and the Company names random. The number of entries per company varies also. I want to be able to calculate the amount due for each company over a thee month period between certain varied dates. Questions 1. Is there a way to enter the dates to calculate between. 2. What is the best way to perform the calculations for each company?...

escaping special characters with DataSet.WriteXml?
Howdy, Is there a way to explicitly escape special characters (particularly & (&amp;) and ' (&apos;)) when writing a data set with the WriteXml method? I know this is supposed to happen automatically (according to MS), but in my case, it doesn't. I'm pulling data from MS-SQL with c#, filling data tables with SqlDataAdapter and a query, adding tables to a DataSet, then WriteXml to a file. Special characters aren't getting escaped. Yes, I've confirmed this by viewing the text directly, not viewing the file through IE, which will convert entities back to chara...

Date Picker issue
Greets to the intelligent among us...... Hi All, I have an anomaly happening with the (date picker??) I load a form to edit a record in History table using among other code, the lines Me.txtFromDate = Me.cboHistEditLookup.Column(4) Me.txtToDate = Me.cboHistEditLookup.Column(5) Then "On Click" to run my update SQL I first do the following check If Me.txtFromDate.Value > Me.txtToDate.Value Then MsgBox " The Ending Date Cannot be Before the Starting Date " Me.txtToDate.SetFocus Exit Sub End If However If when the form opens I click on the txtFromd...

Outlook XP
The 'advanced options' section of the Calendar Options tab is disabled, (greyed out). Any ideas on how to enable it? Registry? Any help appreciated in advance. - Y If the only email account you have is Exchange, it's always disabled, because there's no need to set it. Exchange Server 2000/2003 (but not 5.5) handles the conversion of outbound meeting requests to iCal format. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "Yaz&...