Seperate Date and Time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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).
 
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)
 
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.
 
Back
Top