I have a spreadsheet with a column of data that contains a date and
time, I want to split the time into a separate column but retain the
format of the time as 24 hour clock.
The reason being is I want to create a histogram using the data
analysis Add-In to show how many items were created between certain
timeslots.
If I split the columns manually and then create the histogram manually
it all works, however, when i try to create a macro to do it for me
the format of the time changes to 12 hour clock with an AM/PM marker
in the next column.
This causes the histogram to show everything was created between 00:00
and 12:59:59
I have narrowed the problem down to the Text toColumn code
Selection.TextToColumns Destination:=Range("L1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False,
FieldInfo _
:=Array(Array(1, 4), Array(2, 1)), TrailingMinusNumbers:=True
How the contents are to be parsed into the new second column is
determined by the second part of the above code ... Array(2,1) which
is the XlColumnDataType, the help files show only 9 different
constants none of which allow it as time.
Does anyone know of a way to do this?
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
-~----------~----~----~----~------~----~------~--~---
