Seperate Date and Time
 

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....

Seperate Date and Time
 

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....

Seperate Date and Time
 

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....

Seperate Date and Time
 

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....

Seperate Date and Time
 

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.

Seperate Date and 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....

Seperate Date and Time
 

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....