separating date and time

N

neuman

How can I break a "dd/mm/yyyy hh:mm:ss" field into two separate fields (date
and time)?
I asked this a few weeks ago, but I can't download the original post (and
replies), which brings me to my next question: I reset the newsgroup (OE6)
but I can't download more than ~9800 messages, dating ~6 weeks back. How do
I go further back in time to look for past posts?
Thanks
 
B

Bob Phillips

Neuman,
This is the response that Laura Cook posted


With date and time in column A, in B1 enter:

=INT(A1)

Format as a date. Now in C1 enter:

=A1-B1

Format as a time.

Copy the formulas down as far as needed. If you no longer need column A,
select both column B & C and Copy, then Paste > Special > Values. Now you
can delete column A.

--
HTH,
Laura Cook
Appleton, WI

If you want to go back, use Google Advanced Groups, and you can input
keywords, subject, author, etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
S

Susan

If you have Microsoft Access, import your file into
Access. Next do a make-table query on that table and add
two fields. In the first new field type, "Date:left([field
name that contains the date/time],10)." When you run the
query, this will get you your date. In the second new
field type "Time:right([field that contains the
date/time],8)." When you run the query, this will get you
your time. You do need to put square brackets around the
field name that contains the date/time. Then export the
resulting table back into Excel.
 
M

Martin

Susan,

You will need two new cells, one for date and one for time

If your original date/time was in cell A1 in the format of dd/mm/yyyy
hh:mm:dd then you could create two new cells e.g. B1 and C1. Format B1
as dd/mm/yyyy and format C1 as hh:mm:ss.

In B1 enter the formula =INT(A1)
In C1 enter the Formula =(A1-(INT(A1))

This will split the format for you
 
Top