#### Extracting Time from a cell that has both the date and the time

```Hi Folks,

I could do with some help here please. I am trying to extract the time
only from a cell that has both the date and the time. Can anyone
suggest a solution?

--
------------------------------------------------------------------------

```
 0
9/9/2005 8:20:41 AM
excel.misc 78881 articles. 5 followers.

3 Replies
347 Views

Similar Articles

[PageSpeed] 34

```If you just wish the time to display, set the cell format to Time.

If you wish to use the time portion then =A1-Int(A1) will give you the
time that was in cell A1

> Hi Folks,
>
> I could do with some help here please. I am trying to extract the time
> only from a cell that has both the date and the time. Can anyone
> suggest a solution?
>

--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059

```
 0
9/9/2005 9:12:34 AM
```Just make a copy of the cell (e.g. A1) then in B1 insert =A1 and format B1
like "h:mm"
Stefi

>
> Hi Folks,
>
> I could do with some help here please. I am trying to extract the time
> only from a cell that has both the date and the time. Can anyone
> suggest a solution?
>
>
>
> --
> ------------------------------------------------------------------------
>
>
```
 0
Stefi (275)
9/9/2005 9:46:02 AM
```Hi Hani,

If the date cell is A1, format the destination cell with a suitable time
format and enter the formula:

=A1-INT(A1)

---
Regards,
Norman

wrote in message
>
> Hi Folks,
>
> I could do with some help here please. I am trying to extract the time
> only from a cell that has both the date and the time. Can anyone
> suggest a solution?
>
>
>
> --
> ------------------------------------------------------------------------
> http://www.excelforum.com/member.php?action=getinfo&userid=26794
>

```
 0
normanjones (1047)
9/9/2005 9:59:10 AM

Similar Artilces:

reformat text in a cell
I have 2000+ cells that look like this: UK00001 Geddy Lee 2112 Bytor Ln GLENVIEW, IL 60025-1522 Is there a simple way to add a space after each line? right now when i paste it into a word doc, it shows like this : UK00001 Geddy Lee2112 Bytor LnGLENVIEW, IL 60025-1522 I would like to REFORMAT every cell to look like this with spaces in between to make it readable: UK00001 Geddy Lee 2112 Bytor Ln GLENVIEW, IL 60025-1522 any suggestions? ...

How can I translate a comment to text in a cell?
A cell has existing text in it and it has a comment attached to it. I'd like to append the comment to the end to the existing text in the cell. Has anyone done this already? Is it possible without going into each and every comment? Thanks, Chris ASAP utilities has a UDF formula called "ASAPGetComment()". If you install this utility (which I highly and impartially recomend), you could use this formula to concatenate the two together. http://www.asap-utilities.com "DFIChris" wrote: > A cell has existing text in it and it has a comment attached to it. I...

how do I change picture resolution of all pictures at one time?
I have a large catalogue with over 150 pictures. I have the high res pictures in the file for commercial printing, however would like an e-mailable catalogue and in order to do this I need to change the resolution of all the pictures. Is there a way of doing this all at once? Thanks, Stephen print the cat. to a pdf and email the pdf to your clients. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "Stephen" <Stephen @discussions.microsoft.com> wrote in message news:344EE8B6-8748-4271-B8C6-C77663F7AB80@microsoft.com... > I have a large catalogue...

Averaging Duration of Time: Duration Longer than 24hr
Im trying to average duration of time, and have not found the correct way to format the cell. First, I have a start and end time. I need to calculate the difference between the two in hours, and the duration usually exceeds 24 hours. Then I need to average the time durations. Sample: A1 (start time): 3/27/2008 8:35 Custom format: m/d/yyyy h:mm B1: (end time) 3/30/08 13:00 Custom format: m/d/yyyy h:mm C1: (duration) formula B1-A1 - value = 76:25 Custom format: [h]:mm (this seemed to be the only format that returned the duration in hours appropriately) I average the values in colum...

Print View or Print Paper No start time or end time
How come when i print view or print hard copy of my monthly calendar I dont see the start and end time? "dearcc" <dearcc@discussions.microsoft.com> wrote in message news:0B8CACE7-6A38-4652-8010-B92713583CAE@microsoft.com... > How come when i print view or print hard copy of my monthly calendar I dont > see the start and end time? Outlook version? -- Brian Tillman [MVP-Outlook] They only show if the cell is wide enough. Try Landscape format. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solut...

get end user agreement every time i sign on to outlook must accept
must accept end user agreement every time i get on outlook since i downloaded windows 7 and reloaded office 2003 Start Outlook or any other Office 2003 application with administrator privileges once and accept the EULA. For step-by-step instructions see; http://www.msoutlook.info/question/166 -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "accept end user agreement before submitt&qu...

Dates in excel curiouser and curiouser
Hi folks, I am creating a time sheet but there are some strange problems, notably it calculates the time fine until thw total is over 24 hours and for some reason the minutes are getting rounded up despite there being 2 decimal places...eg 6 minutes becomes hh:10. Very odd and any help would be appreciated Cells are formated as hh:mm. Formulae are either a) (Start Time - End time )-(Lunch End-Lunch start) or sum((End Time - Lunch end)*24,(lunch start-start time)*24)) Many thanks, Danny Sounds like a problem with the time format. Something like hh:00 instead of hh:mm. Can you check th...

How can I count dates if few duplicates in a column
I entered few dates in Column C3 to C20, few of them are duplicates. How can I count total number of dates (excluding duplicates), duplicates should be count 1. Try the below =SUMPRODUCT(--(C3:C20<>""),1/COUNTIF(C3:C20,C3:C20&"")) -- Jacob (MVP - Excel) "Tariq Aziz" wrote: > I entered few dates in Column C3 to C20, few of them are duplicates. > How can I count total number of dates (excluding duplicates), duplicates > should be count 1. Hi, Try this =SUMPRODUCT((C3:C20<>"")/COUNTIF(C3:C20,C3:C20&am...

MIN ingnoring zero sum in an range of cells
I have a spread sheet where I need to use =min(a1:a10,b1:b10) but ignoring any cells with a zero in the range. Please hel. I have been searching the groups for hours. Tks in advance On Dec 22, 3:18=A0pm, Gulicio <michael.gul...@snet.net> wrote: > I have a spread sheet where I need to use =3Dmin(a1:a10,b1:b10) but > ignoring any cells with a zero in the range. Please hel. I have been > searching the groups for hours. Tks in advance Finally found answer that worked for thoose still looking here it is: try the following array formula (entered with CTRL+SHIFT+ENTER): ...

Calendar default date
Using excels calendar on a worksheet works great. However I would lik it to default to todays date. I would think this would be the defaul but I guess not. I have inserted an Calendar 8.0 object. I have inserted the following code in the worksheets code. Private Sub Calendar1_Click() 'ActiveCell.NumberFormat = "m/d/yyyy" ActiveCell = Calendar1.Value Calendar1.Visible = False Range("e27").Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 2 And Target.Row = 27 Then Range("c27").activate ActiveCell.Font.Size = 12 ...

how to insert month date year and day of week
I use Win XP Home Edition, SP2, Excel 2003 Can someone inform me how to put Month, date, year and day of the week in cell. I'd like to insert for example, 05/18/06 Wed <format><cells><number><custum> enter mm/dd/yy ddd "Sachi Noma" wrote: > I use Win XP Home Edition, SP2, Excel 2003 > > Can someone inform me how to put Month, date, year and day of the week in > cell. I'd like to insert for example, 05/18/06 Wed > > > Beautiful, thank you so much Sachi from Japan "bj" <bj@discussions.microsoft.com> wro...

graph changed from the last time I worked with it
I have a simple line graph and when I went back to it the dates in the date column had changed to sequential numbers eg. 39088, 39089, . . . and Date now appears in the legend. I tried to get back to hoe I set it up with Wizard but only am able to get wizard to take me to a new setup. Hi, I'm not clear on the question, but here are a few ideas: 1. You can't get back into the charting wizard unless the chart is selected. 2. If you are asking about the number 39088 - these just need to be formatted as dates, Excel stores dates as integers and times as decimals, so together y...

Using a Message Box to display �Random Quotation� stored in cells
Hi Can any one help me with this one ? What I�m trying to do is to click on a button which runs a Macro whic in turn selects a random quotation i.e. �to be or not to be�.� whic is already stored in a cell. I have about twenty quotes but I need to added to them all the time s the list would keep on growing Any help would be greatly appreciated Many thanks Ro -- Message posted from http://www.ExcelForum.com Hi Rob if your quotes are in column A of the active sheet try the following code (assign this to a button on the active sheet): Private Sub CommandButton1_Click() Dim wks As Wor...

Error in sum function when using merged cells
Help! I recently discovered an error in my shipping worksheet. sometimes need to merge certain cells when I am combining items fro different purchase orders into the same shipment. However, when totaled the column (SUM) containing the merged cells, it delivers wrong answer. Specifically column F and column H that contain the merged cells don' add up correctly. Column F is +1 more than it should be. Column H i +125 more than it should be. I have been using this feature for about 6 months, and this is th first time it did this. Any ideas why the error +----------------------...

Hi all, I have a question about autocoloring cells, rows, columns, etc. This is not about conditional formatting - it's about, for example, coloring every other row automatically per some function or style setup. The example I have is a row-based database of recruiters. As my contacts list grows and shrinks, I have been having to manually recolor every other row light blue for visibility purposes. Not that much of a hassle but (a) I'd prefer not to do it and (b) if Excel has a way to do it for me, so much the better, I learn a new feature of Excel. Is there a way that Excel can a...

Automatically resizing cells
Hi, I am trying to figure out a way that would automatically resize cell height once I enter additional information and part of the text disappears from the screen. Currently it appears that the only way to do that is to double click on the bottom border of the cell I am on. I am using MS Excel 2000. Appreciate you feedback, Jason "JJ" <Iam@cyberspace.net> wrote in message news:Ow7YDMfLEHA.624@TK2MSFTNGP11.phx.gbl... > Hi, > > I am trying to figure out a way that would automatically resize cell height > once I enter additional information and part of the text...

How to insert small colored dots or rectangles in cells of excel
I am trying to insert colored dots or rectangles in cells of excel. How to do this? -- George You could select the cell and then Insert|Symbol and select Webdings or Wingdings etc. and then format the cell font color to whatever you want. "George A. Yorks" wrote: > I am trying to insert colored dots or rectangles in cells of excel. How to > do this? > -- > George ...

Date Increase
I currently have a form for entering current passwords for systems. I have a field to enter the date the password was last changed, and another field for the date the password expires, which is every 30 days. Is there anyway i can set the date password expires field to update automatically? The 2nd field is just calculated from the 1st and better left as a calculation in a query rather than a field in a table. There are no triggers at the table level in Access. Dan Wood wrote: >I currently have a form for entering current passwords for systems. I have a >field to enter...

How can i add a print-and save-date field in Excel as in Word?
In word, you can add a Field of the print-date and save-date. I want to do the same on an Excel document ...

Summing individual digits in a cell
Hi there, I want a formula to add the digits of a cell e.g. 1234, the total would be 10, tried this forumla 'SUM(1*MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))' and resulted in answer '1'. Please, help, i am in urgent need. Thanks Rashi Have a look at http://excel.tips.net/Pages/T002424_Summing_Digits_in_a_Value.html Success!!! -- met vriendelijke groetjes freddy.van.looy@telenet.be <rttaksali@gmail.com> schreef in bericht news:050470d2-61b0-48c0-9aa1-313250362b75@u65g2000hsc.googlegroups.com... > Hi there, > > I want a formula to add the digit...

Time and Billing report
Is there a system for tracking Time and Billing for Activities in CRM. system should be able to flag the activity as billable, and time entered for the activity would be multiplied by billing rate. Then a report would be generated for all billable time for a given period. Rreport must be a detailed report of activity with notes, and with totals by Customer. Anything like that available? I think there is no function in CRM for this. My company just coded a billing system. "WebDesignGeek" wrote: > Is there a system for tracking Time and Billing for Activities in CRM. > &...

annoying updating time! #2
Ok the first solution posted worked for me. I want a time stamp for a LOT of cells. the scenario is student give their ID number. they type it in and i the next cell it stamps the time. bearing in mind there could be hundred students. So do i put a range in the ""? at teh moment its "b3 do i enter "b3-B1003" ?? thanks so far guys/gal -- AJSulliva ----------------------------------------------------------------------- AJSullivan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1652 View this thread: http://www.excelforum.com/showthread...

run time error 10-22-03
I am having a lot of trouble when I open up word I get run time error 52 in VB. I have tried uninstalling word and reinstalling it. WE have tried deleting the macro but still to no avail can someone help me please? ...

cannot restore pst files.... heartattack time
For the first time I archived Outlook Today and my Personal Folders, date of today. I did not know it all would be swept away into the dungeon... I also have a separate (automatic) Outlook.pst file (304.049 KB years of research) which is still keeping up with the clock... but I cannot restore any of them. The only thing has been the duplicating of Outlook Today Folders (NOT Personal folders), but all folders are empty except for Contacts and Calendar. I have used the wizard and also followed the instructions below... no postive results. I use Windows ME and Outlook 2000. please help! jopie ...

Easy copying of blank cell in column to cell below
I know you can cut and paste one data in a column to a blank cell below it. How can you copy a single item like "tag number" to multiple blank lines in records below it in the same column below it, without doing it one line at a time. Access cut and paste only seems to work on one "copied item" to a single blank cell below it. You can copy multiple records to multiple records by highlighting the data, copying, then highlighting the target records and pasting. -- KARL DEWEY Build a little - Test a little "Steve" wrote: > I know you can cut and past...