Date and Time
in [Functions]
|
From: Dave Peterson on 14 May 2010 19:16 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). On 05/14/2010 17:34, Jen_T wrote: > 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: Dave Peterson on 14 May 2010 19:16 Remember to format the cell as a time. On 05/14/2010 17:51, Jen_T wrote: > 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. >>> >>> >>>
From: Ashish Mathur on 14 May 2010 20:14 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 "Jen_T" <JenT(a)discussions.microsoft.com> wrote in message news:47362538-A1EE-4C0C-887A-9C6CF3A55B2A(a)microsoft.com... > 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: Chip Pearson on 14 May 2010 20:25 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 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. > >
First
|
Prev
|
Pages: 1 2 Prev: COUNT FORMULA??? Next: Find first and last cells in range, return info beside |