Rounding in Excel

I need to round a time to the nearest 30 minutes in Excel
0
dG (28)
12/5/2005 8:24:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
657 Views

Similar Articles

[PageSpeed] 12

One way:

XL stores times as fractional days, so since 30 minutes is 1/48 of a day:

    =ROUND(A1*48,0)/48

format as a time.


In article <85471F3C-009B-468C-826A-FE979AE7E9BD@microsoft.com>,
 "DG" <DG@discussions.microsoft.com> wrote:

> I need to round a time to the nearest 30 minutes in Excel
0
jemcgimpsey (6723)
12/5/2005 8:34:59 PM
Reply:

Similar Artilces:

Excel #80
I must have clicked the e-mail button, so I see the e-mail toolbars when I go to one of the spreadsheets. HOw can I get rid of these e-mail toolbars as I do not want to send the spreadsheet to anyone? if you get the toolbar everytime you open excel, go to the menu View Toolbars. unselect the toolbars that you do not want to see. if you mean that you only see the toolbar when you open a *particular workbook, it may be that the workbook has a macro that creates such toolbar. you can try to open the workbook's VBA and edit out th section that creates the toolbar -- Message posted from ...

rounding #9
is there a way to have a column round automatically instead of having to round onto another column? I don't want to add another column to just be able to round. -- denhar ------------------------------------------------------------------------ denhar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24089 View this thread: http://www.excelforum.com/showthread.php?threadid=378356 If the column contains formulas, the formulas can incorporate the Round() function within themselves. For example, if the formula is: =A1*B1 it could be revised to: =ROUND(A1*...

Can Excel Work like Access
My Question is....Can Excel Work Like Access I need two people to be able to enter data into the same workbook a one time. I do not want to deal with the hassle of access and the fac that i do not have access on one computer, limits my options. thanks for any help you can send my way -- Bostick7 ----------------------------------------------------------------------- Bostick73's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1443 View this thread: http://www.excelforum.com/showthread.php?threadid=27221 In essence the answer to your question is Yes. Excel can ...

MS Excel: Linking spreadsheet and Absolute and Relative Paths
Our staff uses several spreadsheets, many of which are linked to SEVERAL other spreadsheets. Of course, all of these documents are stored on the network. The problem we are having is the absolute path that the link uses. Some users will access these file via (e.g.) 'S:\excel documents', while others will use 'R:\excel documents' to connect. Each time a user connects there is a 50-50 chance they will have to update the links. ** What a hassle** Is there any way to use a relative path to link spreadsheets? Using a UNC path isn't possible due to security restrictions and cha...

Accessing the content of an excel sheet
Is is possible to programmatically access the content of an excel sheet without an ODBC driver or any other driver? This is what I would like to accomplish: At a certain point in my application my users must select an excel sheet on their computer. Access will access the excel sheet, read and act on the content. The location of this excel file will vary. How can I accomplish this? It does not look like an ODBC driver is the way to go. Thanks for your help One approach would be Automation. See whether my Access Answers column in the July, 2005 issue of "Smart Access&qu...

Help in Excel #4
When i type then format an excel sheet it changes when i go to preview it. Some cells have two lines but in the preview and print it all goes onto one and leaves big spaces. Is there an option to print and view as typed. I thought there was a format option but I have tried everything and nothing.... Please anybody help ...

round() ?
Hello How can i round float to 2 digits after '.' ? Thanx Quick and Dirty: Multiply by 100, add 0.5 (to round up correctly). convert to long, then back to float, then divide by 100. This is limited to values that will fit within a long, and is not efficient. It is reliable. also: float val; val = val - fmod(val, .01); Or, the real thing: char *_fcvt( double value, int count, int *dec, int *sign ); Which is a char string, which you can then feed to atof() and get your rounded value back. Alas, beware errors in precision limits! Balboos user@domain.invalid wrote: >...

excel LOOKUP( ) error #3
the data matches up and for all other cells in the column, the correc value is returned. in the first row however, N/A is returne -- apple ----------------------------------------------------------------------- apples's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1529 View this thread: http://www.excelforum.com/showthread.php?threadid=26922 ...

Totals Row in Excel 2007
How do you enable the Totals Row in an existing table? This is a free translation from Hebrew: Right click a cell of the Table > 'Table" > 'Summarize Line' Micky "Josh Doten" wrote: > How do you enable the Totals Row in an existing table? ...

Rounding question
I'm using Excel 2007 but 'save as' 97-2003 as colleagues have different versions. All number formats are set to 2 decimal places. I'm finding that percentage calculations are rounding up to 2 decimal places but when the result in currency is subtracted from another figure the sum is rounded down. This gives results such as 6 - 3 = 4. I'm sure there must be a simple answer but I can't find it. Hoping someone here knows, as checking all simple calcuations is getting ridiculous ! Carrie "Carrie" <pantscarolyn.smith2@ntlworld.com> w...

can I automatically insert a page break after subtotals in Excel?
Trying to take a large sheet of data, subtotal and print separate sheet for each subtotaled group without manually inserting page break before printing. Using Excel 2002. thanks Hi Teri when you do your subtotals, check the box for inserting a page break between groups "Teri" wrote: > Trying to take a large sheet of data, subtotal and print separate sheet for > each subtotaled group without manually inserting page break before printing. > Using Excel 2002. thanks ...

how do i superscript part of a cell in MS Excel? #2
When I use the superscript option after selecting just a portion of the cell, the superscript appears during edit mode only. How do I get it so it appears as superscript on the spreadsheet or when printing? Again, I'm only trying to superscript part of a cell's contents, not the entire cell. Did you hit enter have you applied the format change? And you can't do this kind of formatting to formulas or real numbers. It has to be text. If you do this a lot... John Walkenbach has a helpful addin that you may like: http://j-walk.com/ss/excel/files/supersub.htm allan wrote: >...

excel 97 #19
I can't seem to understand the whole process for entering data in a worksheet and having it connect to a form (or vice versa). Can anyone give me a simple step by step on how to do this? I'm trying to create invoices. ...

Excel Edit and Replace
The edit and repalce function is not working liek I think it should. I have one workbook with multiple worksheets in it. Each month I copy the prior month's worksheet and then rename it to the current month. I have a check request that has links to these monthly worksheets. When I try to update the links, Excel forces me to navigate through my directories until I come to the workbook I am in, which is already open, and then click on the name of the workbook before it will update the link. I. E. I want to replace the "Mar" in "Mar 10 Fees" with "A...

Locking Excel
I am in the military, and I made a budget worksheet to use for work. set everything up so it adds and subtracts all the right dolla amounts. My only question is about the cells. In the first cell I hav Name:, when I open the sheet and try to add the soldiers name i highlights the whole cell. If I put the name in there it deletes th Name section. I know if you double click in the cell you can add inf without writing over the original data. I was wondering if there was a way to set it up so that the dat already in there (Name:) is protected. Someone told me in order to d that I needed to make...

Problem with rounding currency values
Hi, I am using the Sales Global Procedure SOP_Calculate_Trade_Discount_Split. This procedure returns the line trade discount. The line trade discount returned is rounded off. eg: if value is 6.76 then it returns 7.00. Is there some place where i can specify to turn off the rounding off? The output type is Currency. Any help is greatly appreciated. Thanks & Regards, Sup ...

Filename at the very top of Microsoft Excel Window
At the very top of the Excel window where you have the Microsoft Excel icon, it contains the words 'Microsoft Excel - filename'. Until yesterday it was showing the words 'Microsoft Excel - ' with the filename. Yesterday it contained the '.xls' at the end of the filename and today it only contains the filename with no file extension. I have an automated program that looks for the filename with the file extsnsion to close down excel and it's not working. How do you control the display of the file extension or not? Hi, An unsaved file will not have an e...

One Note Plugin For Excel
I've found the two tool plugins for Outlook and Explorer pretty helpful and was wondering if you guys could do the same sort of plugin for Excel. ...

How can I chart a multi-year time series in Excel?
I have a multi-year time series that I am charting. I can produce a simple line graph with a time-scale X-axis no problem, but I would like a multi-line chart, with each year being a different line. This would be a powerful way of comparing year-on-year changes. The base data is a series of measurements taken at irregular dates. For analysis purposes I am using the average value halfway between each date pair. ...

Excel 2003
Excel 2003 - sheets all coming up protected and Read Only My client has Office XP and all her spreadsheets that she is creating are read only right away. When she trys to do anything with them she gets" "The cell or chart you are trying to change is protected and therefore read-only. To modify a protected cell or chart, first remove protection using unprotect sheet command. You may be prompted for a password." She is prompted for a password which she did not create. Any idea how to fix this? mick2767@hotmail.com So she starts Excel and the default initial empty wo...

Round Up and Round Down Time
Good Morning & a Happy New Year to all subscribers in this group. I want to round up Start times and round down Finish times to the next or previous 15 minutes in my spreadsheet. I have found MROUND but this does not work for me in all cases. There does not seem to be MROUNDUP or MROUNDDOWN available. I can get the results I want using a VLOOKUP table but is there another way? Regards to all, Dave Moore =FLOOR(A1,TIME(,15,)) =CEILING(A1,TIME(,15,)) -- David Biddulph DaveMoore wrote: > Good Morning & a Happy New Year to all subscribers in this group. > >...

How do I set up an Excel column to not allow duplicate entries (m.
I'd like to make sure a user can't enter duplicate data into a particualar column in an excel spreadsheet. Any help is REALLY appreciated! Chip Pearson's site explains how to do that: http://www.cpearson.com/excel/NoDupEntry.htm "Staci2s" wrote: > I'd like to make sure a user can't enter duplicate data into a particualar > column in an excel spreadsheet. Any help is REALLY appreciated! Wow! Thanks for the speedy reply. Got it all figured out! "Duke Carey" wrote: > Chip Pearson's site explains how to do that: > > http://w...

resize edit links Dialogue Box in Excel 2007
I am using Excel 2007 and wish to resize edit links Dialogue Box when there are multiple links to edit. Not able to do so unlike in Excel 2003. Is this is any limitation in Excel 2007 for this particular feature. Example if we have more than 80+ files which are linked and we cannot edit the links. Please let me the workaround. I am having the exact same problem - anybody have a solution? "Jagdeep" wrote: > I am using Excel 2007 and wish to resize edit links Dialogue Box when there > are multiple links to edit. Not able to do so unlike in Excel 2003. > Is this is any l...

Text boxes in Excel 2003 chart won't print
Is this a bug in Excel 2003? I have a workbook with lots of charts, each on their own chart worksheet. On most of these charts, I'd drawin text boxes, arrows, lines, etc. I've done this for years with no issue. However on this one file, none of the text boxes will print, they don't show up in a .pdf conversion, and they don't show up in a copy/paste of the chart (to another application). Any ideas? Thanks in advance Chris, If you double click the text box(es) & go to the properties tab, make sure the "Print object" box is checked. "ChrisM" wrot...

formula in excell
I'm trying to find the formula that will calculate the percentage gain or loss, between two numbers One way: =B1/A1 - 1 (which is a bit more efficent than (B1-A1)/A1, since A1 only has to be looked up once). Format as a percentage. In article <03bd01c378a3$e0e0b110$a401280a@phx.gbl>, "Bill" <spencemi@hotmail.com> wrote: > I'm trying to find the formula that will calculate the > percentage gain or loss, between two numbers ...