Prev: COUNT FORMULA???
Next: Find first and last cells in range, return info beside
From: Jen_T on 14 May 2010 17:30
I have cells in a worksheet that are formatted as date but still show as date
and time. How do I pull out the time.

E.g.
04/07/2009 1:00:00 AM
I have tried the =left() and also =right () formulas with no luck.



From: Gord Dibben on 14 May 2010 18:17
Date and time are just numbers underneath the formatting.

04/07/2009 1:00:00 AM is 39910.04167

Format the cell to General to see that number.

Use Data>Text to Columns and delimit by decimal point.

In third step choose the right hand column and "Skip"

Finish

Re-format the cell.


Gord Dibben MS Excel MVP

On Fri, 14 May 2010 14:30:01 -0700, Jen_T <JenT(a)discussions.microsoft.com>
wrote:

>I have cells in a worksheet that are formatted as date but still show as date
>and time. How do I pull out the time.
>
>E.g.
>04/07/2009 1:00:00 AM
>I have tried the =left() and also =right () formulas with no luck.
>
>

From: Teethless mama on 14 May 2010 18:22
=MOD(A1,1)

Format as h:mm

"Jen_T" wrote:

> I have cells in a worksheet that are formatted as date but still show as date
> and time. How do I pull out the time.
>
> E.g.
> 04/07/2009 1:00:00 AM
> I have tried the =left() and also =right () formulas with no luck.
>
>
>
From: Jen_T on 14 May 2010 18:34
Thank you so much.I am not understanding how this works, but it does. Do you
have time explain this function ?

"Teethless mama" wrote:

> =MOD(A1,1)
>
> Format as h:mm
>
> "Jen_T" wrote:
>
> > I have cells in a worksheet that are formatted as date but still show as date
> > and time. How do I pull out the time.
> >
> > E.g.
> > 04/07/2009 1:00:00 AM
> > I have tried the =left() and also =right () formulas with no luck.
> >
> >
> >
From: Jen_T on 14 May 2010 18:51
Actually looking at this it doe snot pull in the date, the results pull in
01/00/1900. Any thoughts?

"Teethless mama" wrote:

> =MOD(A1,1)
>
> Format as h:mm
>
> "Jen_T" wrote:
>
> > I have cells in a worksheet that are formatted as date but still show as date
> > and time. How do I pull out the time.
> >
> > E.g.
> > 04/07/2009 1:00:00 AM
> > I have tried the =left() and also =right () formulas with no luck.
> >
> >
> >
 |  Next  |  Last
Pages: 1 2
Prev: COUNT FORMULA???
Next: Find first and last cells in range, return info beside