Date and Time

[Jen_T]

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.

 

[Gord Dibben]

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

 

[Teethless mama]

=MOD(A1,1)

Format as h:mm

 

[Jen_T]

Thank you so much.I am not understanding how this works, but it does. Do you
have time explain this function ?

 

[Jen_T]

Actually looking at this it doe snot pull in the date, the results pull in
01/00/1900. Any thoughts?

 

[Dave Peterson]

Dates are just whole numbers to excel. So if you type 40312 into a cell, then
format it to show as a date (and you're using the 1904 date system), you'll see
May 14, 2010.

Times are fractions of a day. Noon is 12 hours or =12/24 (or .5).

=mod(a1,1)
give the remainder after you divide a1 by 1 (the fractional part of A1).

So that's just the time.

=int(a1)
would give you the date (with no time or 12:00:00 AM if you format the cell to
show the time in hh:mm:ss).

 

[Dave Peterson]

Remember to format the cell as a time.

Actually looking at this it doe snot pull in the date, the results pull in
01/00/1900. Any thoughts?

 

[Ashish Mathur]

Hi,

You may use Data > Text to columns and give space as a delimiter. You will
get tow column - delete the time column and format the data as dd/m/yyyy

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Chip Pearson]

Your post raises several questions. First, why does the date format
still display the time? No idea, except that perhaps you don't have
the cell formatted the way you think you do. I've never seen such
behavior before.

Next, by "pull out the time", do you mean extract the time to another
cell? In this case, with the original data in A1, use =MOD(A1,1) and
format this cell for time. It will return the time in A1 without any
date value. (Actually, the date value will be 0, which is interpreted
as 0-Jan-1900). If by "pull out the time", you mean to remove the time
component and end up with just a date, use =INT(A1) and format for
Date. This will return midnight of the date in A1.

Both of these formulas change the actual value from A1. Regardless of
any formatting that is applied, formatting alone does not change the
value of a cell. If A1 has 12-May-2010 12:34 PM, changing the format
to a simple date cause the DISPLAY to be just a date, but the VALUE of
the cell still has the time component. This may be important if you
have calculations based on that cell.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com