how to split date and time in one cell into multiple cells
 

I need to perform this function using worksheet commands - I do not want to
use the"manuel entry" of a wizard - I need to "split" date and time (09/24/09
8:40) into two distinct cells...

how to split date and time in one cell into multiple cells
 

Assuming the date/time is a true Excel date/time...

A1 = 09/24/09 8:40

B1: =INT(A1)
Format as Date

C1: =MOD(A1,1)
Format as Time

--
Biff
Microsoft Excel MVP


"Dr. Bill" <Dr. wrote in message
...
I need to perform this function using worksheet commands - I do not want to
use the"manuel entry" of a wizard - I need to "split" date and time
(09/24/09
8:40) into two distinct cells...

how to split date and time in one cell into multiple cells
 

Hi,

I assume these are properly formatted dates/times which we have in (say) A1

Format a1 as

mm/dd/yyyy

in b1 enter the formula =A1
Format b1 as hh:mm

or if you don't want the leading zero format as

h:mm

Drag down as required

Mike

"Dr. Bill" wrote:

I need to perform this function using worksheet commands - I do not want to
use the"manuel entry" of a wizard - I need to "split" date and time (09/24/09
8:40) into two distinct cells...

how to split date and time in one cell into multiple cells
 

Just put =A1 (assuming your date/time is in A1 of course) in both cells and
then format one of the cells as Date and the other as Time.

--
Rick (MVP - Excel)


"Dr. Bill" <Dr. wrote in message
...
I need to perform this function using worksheet commands - I do not want to
use the"manuel entry" of a wizard - I need to "split" date and time
(09/24/09
8:40) into two distinct cells...

how to split date and time in one cell into multiple cells
 

T. Valko wrote:
Assuming the date/time is a true Excel date/time...

A1 = 09/24/09 8:40

B1: =INT(A1)
Format as Date

C1: =MOD(A1,1)
Format as Time



And without assuming, I think you could just add "--" in front of A1 and it
works for either date/time or text entries.

how to split date and time in one cell into multiple cells
 

You don't need the double unary minus. INT and MOD will cope with the text
entries.
--
David Biddulph

"Glenn" wrote in message
...
T. Valko wrote:
Assuming the date/time is a true Excel date/time...

A1 = 09/24/09 8:40

B1: =INT(A1)
Format as Date

C1: =MOD(A1,1)
Format as Time



And without assuming, I think you could just add "--" in front of A1 and
it works for either date/time or text entries.

how to split date and time in one cell into multiple cells
 

Of course, you're right. I swear I tried it and it didn't work...

David Biddulph wrote:
You don't need the double unary minus. INT and MOD will cope with the text
entries.
--
David Biddulph

"Glenn" wrote in message
...
T. Valko wrote:
Assuming the date/time is a true Excel date/time...

A1 = 09/24/09 8:40

B1: =INT(A1)
Format as Date

C1: =MOD(A1,1)
Format as Time


And without assuming, I think you could just add "--" in front of A1 and
it works for either date/time or text entries.