Date and Time 05-14-10

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.



0
Utf
5/14/2010 9:30:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

8 Replies
1090 Views

Similar Articles

[PageSpeed] 18

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

0
Gord
5/14/2010 10:17:14 PM
=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.
> 
> 
> 
0
Utf
5/14/2010 10:22:01 PM
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.
> > 
> > 
> > 
0
Utf
5/14/2010 10:34:01 PM
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.
> > 
> > 
> > 
0
Utf
5/14/2010 10:51:01 PM
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.
>>>
>>>
>>>
0
Dave
5/14/2010 11:16:01 PM
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.
>>>
>>>
>>>
0
Dave
5/14/2010 11:16:56 PM
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@discussions.microsoft.com> wrote in message 
news:47362538-A1EE-4C0C-887A-9C6CF3A55B2A@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.
>
>
> 
0
Ashish
5/15/2010 12:14:02 AM
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@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.
>
>
0
Chip
5/15/2010 12:25:53 AM
Reply:

Similar Artilces:

Grouping dates in pivot table
I am pulling data from a SQL Server database to create a pivot table. Excel (07) is not recognizing the field as a date. This is a field I would like to group by in the pivottable. I am aware of all the techniques to convert this to a date field, but I am searching for an answer as to why XL pivot tables cannot consume the dates directly from a sql query. I have played with bringing the dates back in a number of differenent formats with no success. Any insights into this would be appreciated. Probably coming in as text and yuo may only need to copy an unused cell and paste sp...

how do i change dates on my calendar and keep my pics
Publisher doesn't support this. You would need to do it manually. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com ~pay it forward~ This posting is provided "AS IS" with no warranties, and confers no rights. "Bob T" <Bob T@discussions.microsoft.com> wrote in message news:68671977-F71B-444F-AE72-801D2B359FEE@microsoft.com... > ...

Help with SQL Query 06-30-10
We have a distinct list of email addresses in alpha order and we need to transform it from a single column into a grid of three columns maintaining the alpha order. The list is contained in a temp table inside of our query. We then use the temp table list and perform case statement with a mod on the row_number in a select statement to columnze the data. However, the columnar data contains a null value in two of the three columns and we are needing to remove the nulls and have the actual values on each row in the output. Here is our current sql: CREATE TABLE #tmpTable ( Email_Add...

GP 10.0 Fresh Installation
I am trying to install GP 10.0 Trial Version on my laptop. Once when I tried half-way through the installation I had to abort installation. Subsequently after uninstalling all the installed component I restarted installation. Pre-requisite component installation window appears and installs Dextrity components. Next when I try installation using Autorun.inf file I get error "Installation Failed". If I try invoking Setup.exe directly nothing happens. Can someone help me? Hi pvravindran If this is a French Canadian Installation, I would recommend you contact Strophe (www.strop...

Junk mail #14
Hello Last few days we're getting a lot of regular e-mails moved to Junk-mail folder in Outlook 2003. Can't find any way why this is so, because these are just ordinary mail, without any garbage or other stuff. Junk mail settings are set to low-security, also all Outlook spam updates are installed. Any ideas what could cause this? We're using linux mail server, not MS Exchange. It's strange because after a week or two everything was working fine and there were no changes made to computers, besides critical updates. Are there any problems in general with junk mail filter in Ou...

Question about Date validation
Hi All, I'm working on a form for weekly data entry. I'd like to create a date field where the user will enter the date the week ends (ie week ending Friday, June 25th). Is there a way to write code so that Access will check that the day the user inputs is a Friday (and if not they will recieve an error message)?. I know how to program the error message pop-up box, but I have no idea how to write code to check if the date inputted is a Friday. Any help would be greatly appreciated! Thanks Use the WeekDay function If WeekDay(Me.DateField) = 6 Then MsgBox "Error" En...

Date and Time
Is it possible to have a cell show the current date and time without entering it, like in word? Nick If you want the current date and time, updated whenever a calculation occurs, use =NOW(). If you want to enter the current date, without updating, press CTRL+; -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com chip@cpearson.com "Lord Of The Morning" <Not@aol.com> wrote in message news:vmjpgijrjvs5c5@corp.supernews.com... > Is it possible to have a cell show the current date and time without > entering it, like in word? > > Nick > >...

Sending Attachments #14
In Outlook 2000, when I added an attachment to an e-mail message, the name of the attachment neatly appeared on a line just under the subject line. In Outlook 2003, when I add an attachment, a rather large icon representing the attachment appears plonk in the middle of the message, wherever the cursor was last located, just before I clicked on the attachment button. I've looked, but can't find any way to make Outlook 2003 behave as Outlook 2000 did. Is there a way? If so, what is it? Thanks. -- Bob S. "Bob" <xxxx@xxxxx.com> wrote in message news:esskk2tc2796h9en5...

Date #9
One column has the date which I enter manually. If I type 15may it converts immediately to 15-May which is great. But how can I get the line to enter dates automatically? What I need is to enter one date (say, 20 May) and for all lines below to automatically add 1 day at a time, either at the time of starting the line, or even before in one go. Thank you very much JB You can use a formula, if you add the date in A2, in A3 put =IF(A2="","",A2+1) copy down as long as needed and format the cells the same way as in A2 -- Regards, Peo Sjoblom "JB"...

Date Problem !!
hi there i have a big problem with the dates. i enter 09/10/2005 in a cell and it changes to 10/09/2005 !! big problem. it shows 10/09/2005 in the cell but 09/10/2005 in the formula bar !! 2 different dates very confusing is there a way of telling it to use just one kind ??? thanks -- cassy01 ------------------------------------------------------------------------ cassy01's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=780 View this thread: http://www.excelforum.com/showthread.php?threadid=473175 It's the same date, it's just formatted differently o...

dates
Hi there - I am a new user, have only started using excel 2 days ago but am really enjoying it. I have managed to create timesheets that automatically calculate the time spent on a project. Anyway, enjoying it very much. So, I have dates running down the left hand column (A), eg, 05.10.05. Currently I am inputting these by hand because if I drag that cell down it creates 05.10.06, 05.10.07 etc. I have messed with the 'custom' setting and changed it to 'dd.mm.yy' but this has not solved it. Any help much appreciated. By the way, it's excel 2001 for mac. Tom -- small ...

Hiding tasks with due dates in the distant future
I'd like to display only my tasks for the next month or so. How can I filter out those that are in the distant future or recurring tasks that won't be due until next year or the year after? Hi, Goto the Tasks folder. Goto the Customize current view. Click on the Filter Button. Goto the Advanced Tab. Under define more criteria, clickon the Feilds and then select All Task Feilds and then select Due Date. Then under the condition Select Between and then in the Value enter the range of dates ( say 3/8/2004 and 3/10/2004 ). Hope this helps !!! With Regards, Sudharson.AN "Ambe...

Time wrong
The clock on my PC is correct however when I send to someone it shows a time 3 hours in advance. Anyone have any thoughts how to correct? ...

Historical Stock Status As of Date
This is a multi-part message in MIME format. ------=_NextPart_000_00A5_01C8845C.ADFC9B50 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I'd like to know whether anyone has encountered an issue wherein the As = of Date that is printed in the Historical Stock Status Detailed by Item = does not follow the date as indicated in the report options screen. = Currently we are trying to print the said report but no matter what date = we put in, the As of Date that prints on the report is set to 30 Jan = 2008. Is there anything that we...

sync outlook and entourage tasks 02-12-10
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange Is it possible to record a task in outlook and have it show up in entourage, and vice versa...? On 2/12/10 2:29 PM, in article 59bb2be3.-1@webcrossing.JaKIaxP2ac0, "cccryer@officeformac.com" <cccryer@officeformac.com> wrote: > Exchange Is it possible to record a task in outlook and have it show up in > entourage, and vice versa...? Entourage for EWS: Notes and Tasks don't sync? <http://blog.entourage.mvps.org/2010/01/entourage_for_ews_notes_and_ta...

Find the number of years between two dates
I have aroster of players with thier date of births. I would like to automatically add their ages in another column Try this Formula, assuming that the dob is in cell A1 =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" regards Mukesh "duke" wrote: > I have aroster of players with thier date of births. I would like to > automatically add their ages in another column On Fri, 9 Sep 2005 19:47:02 -0700, "duke" <duke@disc...

How can i use a command button to validate date and time
I have two command buttons, each on a different sheet. i need both of them to put the current date in one cell on its sheet and current time in another on its sheet. i also need these cells to be locked so the user can only edit these cells by clicking the command button. Any answers? ...

Trouble with time formats
Any help appreciated! I need to work out the average speed it would take to cover a certain distance. EG. If you covered 10 miles in 28 minutes what would teh average speed be... Column A: 10 miles Column B: 28 minutes Column C: Would return 21.43mph. I then want to to add time increments of say 15 seconds in rows so that I can see that if it took 28 minutes, 45 seconds, the average speed column would return 20.87mph. I can work it out by entering 28.75 for the time (or 70.50 for 1h,10m,30s), but I want to format it as time, i.e: 00:28:45 (or 01:10:30) Is this possible? Thanks...

Running Office 2004 with network homes on 10.4
Hi. Im really at the end of my tether with office now, for well over a year it seems as though no matter what I do office just behaves like a spoilt brat. Were talking 10-20 minute load times just to load an office app. It will sit there and first it say configuring microsoft office components. then through a host of other things until eventually as i said about 20 minutes later it will actually open. I've seen things about fonts before, but i can tell its not just (if at all) this. I have a reasonably fast network, so i cant see how it is network issues. All clients have networ...

IF Statement 02-17-10
The statement below is working good. I need to add a statement to the end that all other numbers will be 200%. Can someone help, please. =(IF(G6=20,"110%",(IF(G6=25,"110%",(IF(G6=50,"110%") Thank you Tina Try this nut note a couple of things, There are no a lot less parenthesis, yo had too many Ive removed the quites from the percent answers. Format your formula cell as percent with as many or as few decimal places as you want If the formula works for you then that's fine but it 'could' produce unwanted results. For example ...

CRM Certification 10-25-06
I'm wanting to get certified for "Installing and Configuring CRM 3.0" (exam MB2-421), but I can't seem to find ANY info on the Microsoft site, nor can I find training materials. Am I missing something? Any ideas where I can get more info on this exam? -- Brandon ================== Presentations Direct - http://www.presentationsdirect.com > Binding Machines http://www.presentationsdirect.com/binding-machines/gbc-binding-machines.asp > Laminators http://www.presentationsdirect.com/laminators/gbc-laminators.asp > Paper Shredders http://www.p...

Formula question 03-03-10
I am trying to complete the following. In s141 i have a drop list with 'yes' and 'no' as possiblities. In x141 i have a drop list with 3 possible choices, .02 , .04 , or .08 g141 is the originating cell p159 is the answer cell. if s141 remains empty or has 'no' chosen from the drop list i need p159 to remain blank, however if 'yes' is in s141 then i need the answer from g141 multiplied by .02 or .04 or .08 (which ever one is chosen from the drop list in x141) to be displayed in p159. Can anybody help? Thanks in advance. Scoob...

Lebans tooltip control 10-28-07
I am trying to use the tooltip control on a form with a tab control, on which are the other controls I want to show information for. On controls that are outside the tab control, it works fine; on controls inside the tab control, the tooltip shows on text boxes only when they have the focus, and on check boxes not at all. I've tried every naming scheme for controls I can think of, without any luck. Anybody have any ideas about this??? Is it the same problem as using a subform???? I am using ACCESS 2003 -- Jack Walsh ...

date confusion
hi im new to excel 2007 i want to ask that when i type 7/4 in a cell excel thinks that the slash is division operator so it divides it ?? but in different cell it interpret it as date ok another problem is that when I enter 7/4 in the different cell excel interpret it as 4-Jul only but How do I display the year too ? even if i type 7/4/2008 or 7/4/08 it still shows 4-JUL ? Help please thank you very much Format the cell as m/d/yyyy, you created the d-mmm format by just entering 7/4 so you need to reformat the cell to the date format you want. Always use the full date when entering dat...

Todays Date that does not update
I created a macro and a button that when clicked it entered todays date in a cell, which is what I wanted. I used "Today( )". Problem is the date keeps updating to todays date and I want it to stay the date I entered it in. Is there some other value or formula to do this so it does not update everyday? Thanks!! You need to enter the date itself. Any function will always update to the current date. Ctrl+; will enter the current date in a cell. In VBA, use Range("A1").Value = Date -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC...