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?

Thanks in advance. :confused:


-- 
Hani Muhtadi
------------------------------------------------------------------------
Hani Muhtadi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26794
View this thread: http://www.excelforum.com/showthread.php?threadid=466177

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

3 Replies
303 Views

Similar Articles

[PageSpeed] 11

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

Hope this answers your question.


Hani Muhtadi Wrote: 
> 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?
> 
> Thanks in advance. :confused:


-- 
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059
View this thread: http://www.excelforum.com/showthread.php?threadid=466177

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


„Hani Muhtadi” ezt írta:

> 
> 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?
> 
> Thanks in advance. :confused:
> 
> 
> -- 
> Hani Muhtadi
> ------------------------------------------------------------------------
> Hani Muhtadi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26794
> View this thread: http://www.excelforum.com/showthread.php?threadid=466177
> 
> 
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



"Hani Muhtadi" <Hani.Muhtadi.1v33mc_1126256719.5169@excelforum-nospam.com> 
wrote in message 
news:Hani.Muhtadi.1v33mc_1126256719.5169@excelforum-nospam.com...
>
> 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?
>
> Thanks in advance. :confused:
>
>
> -- 
> Hani Muhtadi
> ------------------------------------------------------------------------
> Hani Muhtadi's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=26794
> View this thread: http://www.excelforum.com/showthread.php?threadid=466177
> 


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

Similar Artilces:

how to paste formula and retain all or part of the cell references
I have following kind of problem: I want copy a cell from A5 to B5. In A5 I have a formula that takes values from A2 and E5, i.e. "=A2/E5". In the pasted cell the formula changes to corresponding values: "=B2/F5". In some cases this might be what I want, but in current situation I would like to change only column to corresponding - I would liket the resulting cell to include formula: "=B2/E5". How can I do this? Of course, in case of such simple formula it wouldn't be a problem but if I have very long formula with lots of references, it's a nightmare.. A...

Show which cell has MAX, MIN values?
At the bottom of a couple thousand rows of data, I have =MAX and =MIN formulas. Is there some way I could make the cells beneath my MAX and MIN formulas show me the address of which cell has the displayed MAX or MIN value? At least the row number? Ed Ed, To return the row =MATCH(cell with Max or Min value,range starting in row 1,false) or to return the address, say, in Cell N3000, for a value given in N2999 =ADDRESS(MATCH(N2999,N1:NN2998,FALSE),COLUMN(N1)) or to return other matching information, like a name in column A =INDEX(A:A,MATCH(N2999,N1:NN2998,FALSE)) HTH, Bernie MS E...

require cell completion in a form?
I have a very simple form in Excel. I'm not sure if it matters, but the machine I'm on uses Excel 2002, and most of the users are on Excel 2000. The first 2 questions on my form require that the user select from a list of names, and then select their relationship to that person. Then the users answer a series of questions about this person's job performance. I'm using data validation to manage the two drop-down lists - the names and the relationships. However, about a third of my users end up leaving one or both of these cells blank. I have a comment box set up as a remin...

date formulas #5
Please help! I have given an example of part of the spreadsheet i am creating to outline my queery: Report in / Reply due / 1 Sept 2005 / 1 Sept 05 4 Sept 2005 / 18 Sept 05 / 14 Jan 00 / etc, / etc, I have entered the following formula in the 'reply due' column: B1 = A1 + 14 which calculates the date two weeks after the date in the 'report in' column. When i drag the formula down so it applies the entire 'reply due' column the cells aut...

Average range of discontinuous cells
I have a block of columns (P-CT) that I need to average but only every 4th one. Example: I need to average P, T, X, AB, AF, AJ, AN, AR, AV, AZ, BD, BH, BL, BP, BT, BX, CB, CF, CJ, CN, CR. If I just type =AVERAGE(selecting these cells) it gives me 40238 when the only one with a value is cell P. These cells contain dates if anything. They are payment dates. I need the average number of dates it took to pay something. Any ideas for Excel 2003? Thanks. Never mind. I forgot to include my beginning date. It works. Just user error. "Nadine" wrote: > ...

Date formatting in Excel #2
How do I format a cell to return Oct 04 when I type 10-4 in Excel...When I type 10-4, Excel returns Oct 05. Hi When no year is typed, excel would assume it is the current year. You would ahve to type the full date (including 2004) for it to display as Oct 2004. Use Format - cells - date and then select the particular format you want from the options there. >-----Original Message----- >How do I format a cell to return Oct 04 when I type 10-4 in Excel...When I >type 10-4, Excel returns Oct 05. >. > ...

Last cell with data in a range
I enter weekly data into a spreadsheet with a summary page at the front. After every week, when new data is entered, I want the formula at the front to use the cell with the new data instead of me having to change the existing formula. For example, my data looks like this: A B C 276 300 421 175 0 0 0 I need a formula that will automatically detect the last number >0 in column C. Thank you for your assistance! =Countif(C2:C1000,">0") so your main page formula might be something like: =...

Date Last Sold
Date last sold should be tracked in the item quantities file and the item master. This is a common query request (show me all items we haven't sold in the past two years). Having to go to the sales line item history makes for a slow, complicated solution. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click...

RMS should allow you to choose more than one item at a time
RMS should allow you to choose more than one item at a time, this would be helpful in creating PO's. Mutiple item selection is a standard in every software; you really dropped the ball with this one. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.micr...

display cell value in msgbox formatted as %
Hi I have been trying to come up with a way to display a cell value in a msgbox so that it formats properly as a percent. I have tried: Productivity = Format(Range("A1").Value, "###,# %") Msgbox Productivity This always gives me a leading 0 (e.g 015%) and I want it to display 15.0%. So I tried this: Productivity = Format(CStr(Range("A1").Value) * 100, "#,###.0") & "%" and it works ok but... I want to use the value of productivity in computations - which I can't formatted as a string ... Am I missing something - or is it as simple...

Can multiple cell results be displayed in a single cell?
Is it possible to display the contents of multiple separate cells in another single cell? If the results of several cells say B1, B2, and B3 are the amounts $10, $15 and $20, I would like to display them in cell A1 as follows: $10 $15 $20 I could do this statically by using text and the 'Alt|Enter' function within the cell. However, these amounts will change from time to time so I'd like a link. Is this possible or not? Thanks for any help. Brett Brett One way: In A1: ="$"&B1&CHAR(10)&"$"&B2&CHAR(10)&"$"&B3&am...

Using color to ignore cells in a formula?????
Is it possible to set up a formula in excel to ingore certain cells if you should change the color of that cell to a color. Example: =sum(a1:a50) if cell A25 was blue how can I ignore it so that it is not calculated? Mike A. Hi only with VBA. See: http://www.cpearson.com/excel/colors.htm and http://www.xldynamic.com/source/xld.ColourCounter.html -- Regards Frank Kabel Frankfurt, Germany "Mike A." <MikeA@discussions.microsoft.com> schrieb im Newsbeitrag news:65C99320-9FE3-4E11-81C2-C262B82B565B@microsoft.com... > Is it possible to set up a formula in excel to ingore cer...

time calculation
I need to do a check on the time.. if it is after midnight but before 5am then I need to minus 1 day off of the date. How could I do this? Hi If the Date and Time is in cell A1, use =IF(AND(MOD(A1,1)>VALUE("12:00 AM"),MOD(A1,1)<VALUE("05:00 AM")),A1+1,A1) ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** ...shortened versions would be =A1+AND(MOD(A1,1)>VALUE("12:00 AM"),MOD(A1,1)<VALUE("05:00 AM")) ** Posted via: http://www.ozgrid...

Excel: When printing some cells will not print text in them
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) When I print an Excel 08 spread sheet, two of the cells that have text in them will not print the text in the cell. All the other cells with text prints fine. The text does not even show up on print preview in the print dialogue box. Any suggestions on how to get it to include the text in these two cells? ...

Connecting Cell with Lines
I have seen MSExcel printouts that have dotted or solid lines of different colors connecting different cells in a sheet. Is that something that can be done in MSExcel or would they have been added outside of MSExcel? If it can be done in MSExcel, how? In article <QbYYg.13615$GR.1871@newssvr29.news.prodigy.net>, "jerry" <jerryc314@sbcglobal.net> wrote: >I have seen MSExcel printouts that have dotted or solid lines of different >colors connecting different cells in a sheet. Is that something that can be >done in MSExcel or would they have been added outside of...

Appending data in cells that utilize a Dropdown list.
Hello all, I would like to append entries in cells that utilize a dropdown list. Currently, when I select (in this case names) from my dropdown list I cannot append them if I want to add more than one to a cell. If I select another name from the dropdown, it erases the first entry. Is there some way to append entries within cells that use this form of validation so that I can enter more than one in a cell from the dd list? -- Thanks, Geek using Office XP Professional Hi Geek Debra Dalgleish has an example workbook showing how to do this http://www.contextures.com/excelfiles.html...

Excel Re-Calc causes network traffic and very slow response times.
Platform: Excel Ver 2003 SP2 Windows XP SP2 Microsoft SBS Server 2003 Workstations: P4 2Ghz, 512 Mb Ram Problem: I have an excel file with hundreds of vlookup and indirect formulas. It has no links to any other spreadsheets. The file resides on a shared network drive on our server. When users do a recalc or change a number in the file causing a recalc it takes about 7 seconds... however after a bit of experimentation I find that if we open the file and then disconnect the workstations network cable the recalc takes less than one second ! Tried saving the file to the local drive of a workstati...

Worksheet Change Event log for multiple cells
Dear All, I have a spreadsheet that is available here: http://www.filefactory.com/file/b02e5h4/n/Worksheet_change_event.zip (although I scanned it for viruses please make sure you do it again prior to opening it as I cannot guarantee it's worm free). I would like to create a log file in an additional sheet (hidden probably) that would record every activity from column E after clicking a button assigned to a cell in that column and show these records in a worksheet Totals in a specific row. For example: Column A from a worksheet „Totals” corresponds with column A in ...

passing dates in a conditional sum(if)
I have a workbook with 5 sheets. Sheet1 is Customers Sheet2 is YTD Sales 07 Sheet3 is Total Sales 06 Sheet4 is Total Sales 05 Sheet5 is Total Sales 04 Customer sheet has this basic formula in the columns for each year's sales based on the customer's number and the variable of the month that the sales was done. {=SUM(IF((Custnumber07=A3)*(Month07<=$J$1),amount07))} The problem is that the accounting program exports the invoices with the MMDDYY for each invoice. The $j$1 is the number of the month I limit it up to. I have a column in each sales sheet with the month() function to...

Return value from cell info
Dear expert, If A1 has a figure 39, If A2 has a figure 3, actually it is meant for row 39 and column C. How can I type the function in B1, so that it can capture the info in C39 (column 3 and row 39) Thanks Elton =INDIRECT(ADDRESS(A1,A2,4,1)) -- Regards! Stefi „Elton Law” ezt írta: > Dear expert, > > If A1 has a figure 39, If A2 has a figure 3, actually it is meant for row 39 > and column C. > How can I type the function in B1, so that it can capture the info in C39 > (column 3 and row 39) > Thanks > Elton Thanks. It works .....

Protecting/locking individual cells? + not displaying #N/A on cel
HI, My spreadsheet is using Vookup formulas and I would like to protect the cells with the formulas from others changing them but still alow the oter cells to be free . Such C1 free to place a number that will allow the protected B1 (which has the look up formulae) to have the resulting detail from the look up -locked/protected. ALSO- I have then copied the formulae down to the page and some cells will not have all the time an item in them so now they are showing the #N/A - can this be not displayed? Ta Mike On Sep 10, 8:47=A0am, MikeR-Oz <Mike...@discussions.microsoft.com> w...

returning blank cell in criteria o
Currently I am struckling with advanced filtering. In my criteria range one cell contains an IF formula and the false condition it returns an empty cell "". Problem is now that advanced filter does not treat this as a true blank cell; it now only matches with strings. So if I have figure in this column of my database, it is not selected. I would like to return a true blank from my IF formula, so that everything is matched both strings and figures. Is this possible? Bye Joop One way is with a criteria of: ="<>?*" And that cell with the formula isn't really em...

Time of Last full backup
Can someone tell me what attribute in Active directory does 'Time of Last full backup' hold? On Exchange server database properties on Exchange 2003 you would find this field. Thanks, Easy to find out from the transaction logs (provided you're not using circular logging), as well as from the Application Even Log. Not sure if AD or the store maintain that. -- Bharat Suneja MCSE, MCT -------------------------------- "Henry" <Henry@discussions.microsoft.com> wrote in message news:20E8935A-E4EC-4F3F-B214-FB3338F5F105@microsoft.com... > Can someone tell m...

Cell referenced as text
have cell A1 formatted as a number with 12.34 in it. Want to have cell B1 reference A1, but be formatted as text so it can be used for other purposes. How can this be done? =TEXT(A1,"0.00") -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Mike" <Mike@discussions.microsoft.com> wrote in message news:07DE5D04-B60D-4BE7-B943-8746B6E8D306@microsoft.com... > have cell A1 formatted as a number with 12.34 in it. Want to have cell B1 > reference A1, but be formatted as text so it can be used for other > purposes. > How can this be done? sorry....

Time with the push of one button
Hi all, just wondering if it is possible to enter the time in a cell with only pushing one button. I know I can use ctrl+shift+: to enter the time, but someone told me there is a way to enter the time with only 1 button. Sub NOWTIME() ActiveCell.Value = Format(Now(), "h:mm:ss AM/PM") End Sub Assign to a button on your toolbar. Gord Dibben MS Excel MVP On Thu, 31 Jul 2008 07:35:22 -0700, Dale G <DaleG@discussions.microsoft.com> wrote: >Hi all, just wondering if it is possible to enter the time in a cell with >only pushing one button. I know I can use ctrl+sh...