Hi....

I have the date and time in a cell "9/20/2007 6:49:53 PM". Now, I want the
date and time to be displayed seperately in different cells. Eg:-
B2=9/20/2007(Date only-No Time)
C2=6:49:53 PM(Time only-No Date)

Any help will be appreciated....

One way:

XL stores date/times as offsets from a base date - the integer part
being days, the fractional part being time.

B2: =INT(A2)
C2: =MOD(A2,1)

format B2 as a date, C2 as a time.

In article ,
Wati wrote:

Hi....

I have the date and time in a cell "9/20/2007 6:49:53 PM". Now, I want the
date and time to be displayed seperately in different cells. Eg:-
B2=9/20/2007(Date only-No Time)
C2=6:49:53 PM(Time only-No Date)

Any help will be appreciated....

B2 =INT(A2)
C2 =MOD(A2,1)
Format each cell appropriately.

as an alternative, you could, of course, have =A2 in both B2 and C2, and
format each to show only the relevant part, but that would still leave the
two cells both containing date and time (and merely displaying the subset).
--
David Biddulph

"Wati" wrote in message
...
Hi....

I have the date and time in a cell "9/20/2007 6:49:53 PM". Now, I want
the
date and time to be displayed seperately in different cells. Eg:-
B2=9/20/2007(Date only-No Time)
C2=6:49:53 PM(Time only-No Date)

Any help will be appreciated....

If your value is in A2 and it is a "true" date/time, then in B2 enter:
=A2 and format the cell as a date
and in C2 enter
=A2 and format the cell as a time.


If the value in A2 is just a text string then in B2 enter:
=LEFT(A2,9)
and in C2 enter:
=RIGHT(A2,10)
--
Gary''s Student - gsnu200745


"Wati" wrote:

Hi....

I have the date and time in a cell "9/20/2007 6:49:53 PM". Now, I want the
date and time to be displayed seperately in different cells. Eg:-
B2=9/20/2007(Date only-No Time)
C2=6:49:53 PM(Time only-No Date)

Any help will be appreciated....

Note that this doesn't actually separate the date and time. It will
display only date or time, but calculations based on B2 & C2 will use
the combined date-time.

From your description, that may be acceptable.

In article ,
Gary''s Student wrote:

If your value is in A2 and it is a "true" date/time, then in B2 enter:
=A2 and format the cell as a date
and in C2 enter
=A2 and format the cell as a time.

Thanks a lot, Mate.. Really appreciate your help on this...

"Wati" wrote:

Hi....

I have the date and time in a cell "9/20/2007 6:49:53 PM". Now, I want the
date and time to be displayed seperately in different cells. Eg:-
B2=9/20/2007(Date only-No Time)
C2=6:49:53 PM(Time only-No Date)

Any help will be appreciated....

Working Wonders, Mate... :) U have save a lot of time for me, Mate... Thanks
a lot

"JE McGimpsey" wrote:

One way:

XL stores date/times as offsets from a base date - the integer part
being days, the fractional part being time.

B2: =INT(A2)
C2: =MOD(A2,1)

format B2 as a date, C2 as a time.

In article ,
Wati wrote:

Hi....

I have the date and time in a cell "9/20/2007 6:49:53 PM". Now, I want the
date and time to be displayed seperately in different cells. Eg:-
B2=9/20/2007(Date only-No Time)
C2=6:49:53 PM(Time only-No Date)

Any help will be appreciated....