separating date and time

  • Thread starter Thread starter neuman
  • Start date Start date
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
 
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)
 
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.
 
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
 
Back
Top