I have a column (well several) that has date and time information in
it, such as what is below:
5/24/2004 14:20
5/12/2004 10:22
5/4/2004 13:54
5/11/2004 16:37
5/24/2004 10:09
5/15/2004 12:05
5/3/2004 12:37
5/24/2004 11:45
5/11/2004 11:40
5/18/2004 14:05
5/15/2004 11:45
5/24/2004 13:48
5/14/2004 17:03
5/11/2004 16:26
5/24/2004 14:51
5/3/2004 16:24

I would like to split this column and have the date information in one
column and the time information/data in a different field. In past
cases I have used the =Mid Function but since this is formatted
differently it will not work. When I do the mid function is get
numbers but cannot format them in a date or time that can be read.
This one workbook has around 4000 rows.

I have never written a Macro in Excel so I am totally lost on this one
and I am hoping someone with the skills and knowledge can assist me.

Thanks,

Bill

Hi Bill,

Paste your data into column a, align it left (need to do this for the text
to columns deliniation) and then go to data at the top, specify space
delimited, plonk the line to the right of you 4 in 2004 to tell it where to
stop and click next, then format it as a date, next and finish and you will
get them in two columns. Then you just need to format them in the usual way.
I did it and recorded it in a macro resulting in the following code which
should work:

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth,
_
FieldInfo:=Array(Array(0, 3), Array(10, 1)),
TrailingMinusNumbers:=True
Selection.NumberFormat = "m/d/yyyy"
Columns("B:B").Select
Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"

Hope this help, kind regards, Mark



"Bill" wrote in message
om...
I have a column (well several) that has date and time information in
it, such as what is below:
5/24/2004 14:20
5/12/2004 10:22
5/4/2004 13:54
5/11/2004 16:37
5/24/2004 10:09
5/15/2004 12:05
5/3/2004 12:37
5/24/2004 11:45
5/11/2004 11:40
5/18/2004 14:05
5/15/2004 11:45
5/24/2004 13:48
5/14/2004 17:03
5/11/2004 16:26
5/24/2004 14:51
5/3/2004 16:24

I would like to split this column and have the date information in one
column and the time information/data in a different field. In past
cases I have used the =Mid Function but since this is formatted
differently it will not work. When I do the mid function is get
numbers but cannot format them in a date or time that can be read.
This one workbook has around 4000 rows.

I have never written a Macro in Excel so I am totally lost on this one
and I am hoping someone with the skills and knowledge can assist me.

Thanks,

Bill

Assume first value in A1
in B1
=int(A1)
format as Date

in C1
=A1-int(A1)
format as time

or select the column and do

Data=Text to columns
Select fixed width

Put the line between the date and time values.

then format the first column as Date and the second column as Time

(this will replace you original values.)

--
regards,
Tom Ogilvy


"Bill" wrote in message
om...
I have a column (well several) that has date and time information in
it, such as what is below:
5/24/2004 14:20
5/12/2004 10:22
5/4/2004 13:54
5/11/2004 16:37
5/24/2004 10:09
5/15/2004 12:05
5/3/2004 12:37
5/24/2004 11:45
5/11/2004 11:40
5/18/2004 14:05
5/15/2004 11:45
5/24/2004 13:48
5/14/2004 17:03
5/11/2004 16:26
5/24/2004 14:51
5/3/2004 16:24

I would like to split this column and have the date information in one
column and the time information/data in a different field. In past
cases I have used the =Mid Function but since this is formatted
differently it will not work. When I do the mid function is get
numbers but cannot format them in a date or time that can be read.
This one workbook has around 4000 rows.

I have never written a Macro in Excel so I am totally lost on this one
and I am hoping someone with the skills and knowledge can assist me.

Thanks,

Bill

Bill,

I don't know if will suit your purposes, but it looks like what you want...
Make two identical columns
Format the first column as a date - mm/dd/yyyy
Format the second column as time - h:mm:ss AM/PM

Regards,
Jim Cone
San Francisco, CA

"Bill" wrote in message om...
I have a column (well several) that has date and time information in
it, such as what is below:
5/24/2004 14:20
5/12/2004 10:22
5/4/2004 13:54
5/11/2004 16:37
5/24/2004 10:09
5/15/2004 12:05
5/3/2004 12:37
- snip -
I would like to split this column and have the date information in one
column and the time information/data in a different field. In past
cases I have used the =Mid Function but since this is formatted
differently it will not work. When I do the mid function is get
numbers but cannot format them in a date or time that can be read.
This one workbook has around 4000 rows.
I have never written a Macro in Excel so I am totally lost on this one
and I am hoping someone with the skills and knowledge can assist me.
Thanks,
Bill

Tom, I would like to thank you for your input. Yes it worked! What I
failed to tell you and everyone else was that I needed to average the
column once I completed my task of getting just the time. Using the
format date or time, gave be a visual but would not allow me to do a
=average() and get a correct response.

I would also like to thank everyone else that viewed my posting and
provided input, I love this site.

Again thanks for the input and thanks to everyone else


Bill


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jim thanks for your response and advice, what you recommended is what I
tried yesterday. It gave me the time in the column but my next step was
to average and this time format would not allow me to average correctly.
I heard from Tom and he recommended the =int() and it worked and I was
able to average me column and get what I now believe is the correct
average.

Thanks for your input on my question.

Bill French



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

The result of either method is a time value. You can average it like you
can any time value. Just format the cell containing the average function as
time.

A time value is just a number less than 1, so it certainly be averaged.

This assumes the original values were actual date times.



--
Regards,
Tom Ogilvy

"Bill French" wrote in message
...
Tom, I would like to thank you for your input. Yes it worked! What I
failed to tell you and everyone else was that I needed to average the
column once I completed my task of getting just the time. Using the
format date or time, gave be a visual but would not allow me to do a
=average() and get a correct response.

I would also like to thank everyone else that viewed my posting and
provided input, I love this site.

Again thanks for the input and thanks to everyone else


Bill


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!