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
0
DaveMoore
1/1/2010 11:37:40 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
1974 Views

Similar Articles

[PageSpeed] 32

=FLOOR(A1,TIME(,15,))
=CEILING(A1,TIME(,15,))
--
David Biddulph

DaveMoore wrote:
> 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


0
David
1/1/2010 11:45:51 AM
Beautiful!!
Thank You David


On 1 Jan, 11:45, "David Biddulph" <groups [at] biddulph.org.uk> wrote:
> =FLOOR(A1,TIME(,15,))
> =CEILING(A1,TIME(,15,))
> --
> David Biddulph
>
>
>
> DaveMoore wrote:
> > 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- Hide quoted text -
>
> - Show quoted text -

0
DaveMoore
1/1/2010 12:00:59 PM
Reply:

Similar Artilces:

Time to miles per hour
I have a spreadsheet that holds times for various biking trips. One column has the overall time, formatted for time (e.g.- 5:48:00). The next column has the distance in miles. The third column has a formula that is Time/distance. I get the time it takes to travel a mile. How do I convert that to miles per hour? Thanks Dale In article <BF29234A.3A1B0%drosen32@comcast.net>, Dale <drosen32@comcast.net> wrote: >I have a spreadsheet that holds times for various biking trips. One column >has the overall time, formatted for time (e.g.- 5:48:00). The next column >has the distan...

printing the saved date/time
is the a field code or something to print the save date in a footer i.e there is a field code for the current date in excel &[date], but i would like it to be the last date it was saved thanks in advance!!! You need VBA to do this If you copy this in the ThisWorkbook module it will print the Last Save Time in the RightFooter of every sheet you print Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In ThisWorkbook.Worksheets wkSht.PageSetup.RightFooter = "&8Last Saved : " & _ ActiveWorkbook.BuiltinDoc...

run time error '91'
I get this message after I am asked to load the normal.dot template. I have no idea what it is or how to fix it. The normal template opens automatically when Word starts, so you shouldn't be 'asked to load' the template. However if you are getting vba errors when you open the normal template or when it loads automatically, then you have a macro in that template that is either defective or not applicable to your Word version. The simplest plan is to rename the normal template with Word closed and let Word create a new one. http://www.gmayor.com/what_to_do_when_wor...

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

OWA 2003 Time Zone
Dears, I have exch2k3 on Win2k. My OWA server does not show clients the correct time zone which is causing major calendring proplems. I know this can be adjusted manually by user and that this is a known problem in OWA 2000. Is there is a ny way to have time zone unified for all OWA users. ...

How to Make the "Look For:" Box Appear in Outlook 2003 All the Time?
Hello: My Look For box doesn't stay open all the time. Whenever I close Outlook and log back it I have to do the Cntrl+E to get it to appear. How do I set it to stay open all the time? I'm using Outlook 2003 on WinXP Pro. Thanks in advance! Spudy ...

Cell showing date and time
I have a cell set up as format dd/mm/yyyy hh:mm:ss and the contents are 01/01/1995 00:00:00 In the cell below I put 01/01/1995 01:00:00 then fill the cells for a few hundred. After about 200 cells the value shows as 09/01/1995 10:00:01 - in other words the increment being added for each cell is slightly greater than the desired one hour. I assume this is a lack of precision in the cell value. Is there a way of changing this? I am using Excel 2007. Grateful for advice. Enter the first date/time in A1 and in A2 enter: =A2+TIME(1,0,0) and copy down -- Gary'...

How to monitor group policy changes in real time?
Hi all, I'm interested in developing a real-time monitor that saves all changes to group policy objects. Hopefully, this module will be notified if any admins add/delete/modify GPOs. Can anyone show me some pointers? Thanks! -Simon Simon, Simon wrote: > I'm interested in developing a real-time monitor that saves all > changes to group policy objects. Hopefully, this module will be > notified if any admins add/delete/modify GPOs. > > Can anyone show me some pointers? If yoiu're looking for some change control mechanism, AGPM (Advanced Group...

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

Wrong Sent Time
Yesterday at about 1:00 CDT we requested a supplier use a form in their .ASP application to generate an email confirmation. I had them send it to my office address as well as my home address. The supplier sent the confirmation approximately 90 minutes later. When looking at the email in an Outlook Inbox, the received time is 2:34PM which matches the actual time the message was received. However, the sent time on the opened email is 9:34AM both at the office and at home. In this case, that's 3.5 hours before the request for a confirmation email was sent to the supplier. Looking at the mess...

email receive date and time changes
I don't know if this is an exchange or outlook problem. but my email receive date and time, and whether marked as unread constantly updates. I read the emails then some time later look at inbox and all the emails have new current date and time and are marked unread. how odd I'd appreaciate any help Jon Please provide the following information: 1) Version of Outlook 2) Email environment: Exchange, POP3, etc. 3) Does the user have a PST? If so, what is the size? 4) Are there any error messages/numbers? 5) Have you checked the application event viewer for any Outlook or Off...

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

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

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

Copying formulas to new rows, 20000 times
How can I automatically paste a formula into existing rows. I need to add formulas to 20,000 rows. Help! Put it in the first cell, copy the cell, select the next 19,999 and use Edit / Paste. What is your formula, where is it, and where do you want it, and what do you want it to be when you copy it - Give us an example. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------�------------------------------�---------------- It's easier to beg forgiveness than ask permission :-) ---...

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

Run-time error 3021
I have one form that opens another form to show related records. The 2nd form has a custom record counter that runs the following code: With Me.RecordsetClone .MoveFirst Me.RecCount = Me.CurrentRecord & " of " & .RecordCount End With I got the code from someone here on this forum and it works great as long as the 2nd form has records in it. I originally ran the code in the oncurrent event and also tried onload and afterupdate, but still get the same error. When I debug, it highlights .MoveFirst. I tried taking that out, but then it ...

RPC over HTTPs
Hello, I recently had to reformat my computer. I'm running Windows XP SP2 and all patches, Office 2003 and all patches. When I click on Outlook it opens right away however it takes about 30 seconds for the authentication to pop up and another 30 seconds or more to connect. This didn't use to be an issue before I reformatted. I checked my network environment and my local area network card is the first in priority. I'm not running any new firewall or any QOS other than the default vanilla MS configuration. Any guesses? In news:OMW3QuJJHHA.4112@TK2MSFTNGP04.phx.gb...

Elapsed-Time arithmetic
Hi Everybody, I have to prepare and submit an elapsed-time worksheet... to get paid. The source of the information comes from a calendar program that will export to a CSV file, which I can load into Excel. Sadly, the CSV file does not offer an elapsed time number, just "Start" and "End" fields. So I simply created a third column and formatted the cells in all of them ("Start", "End", and "Elapsed") as: *Time 13:30* My question refers to a new cell, the total of all the elapsed-time cells. How do I format a "total" cell, and wha...

Date time in new message
Is there a way to have the date and time as part of a signature? Or, does someone have a script/macro to do so? J <dstarn@ameritech.net> wrote: > Is there a way to have the date and time as part of a signature? > > Or, does someone have a script/macro to do so? Perhaps something like this: http://www.outlookcode.com/d/code/stampdate.htm -- Brian Tillman Thank you "Brian Tillman" <tillman1952@yahoo.com> wrote in message news:uUkZWvbAHHA.3316@TK2MSFTNGP02.phx.gbl... >J <dstarn@ameritech.net> wrote: > >> Is there a way to have the dat...

Administrative Time in PS 2007
I need to create a report displaying status of the Administrative Time for the managers to keep track of admin time already approved or pending. Administrative Time approved separately from the Timesheet where Timesheet is in the future and waiting for approval. What field in the database gets updated when manager approves Admin time request? Best to re-post this in the newsgroup dedicated to Project Server: microsoft.public.project.server. --rms www.rmschneider.com On 04/03/10 14:59, laguna wrote: > I need to create a report displaying status of the Administra...

Time calculation: billing for minutes
I am trying to enter a start time in one cell, an end time in another, and calculate the total to charge, based on $35 per hour. ie: 9:35 a.m. start, 9:40 a.m. end is 5 minutes. How do I calculate 5 minutes (1/12 of an hour) into percentage, or whatever to end up with the correct answer. (I should come up with $2.92) I'm feeling pretty stupid right about now. -- Kenny Hi Kenny, B23: 09:35 C23: 09:40 D23: 00:05 E23: $ 2.92 B23: 0.39930 C23: 0.402777 D23: =C23-B23+(B23>C23) E23: =D23*24*35 h:mm ...

Rounding numbers in charts
HHi U'm not very good with computers. And I did a spreadsheet on excel and had percentages I wanted to put in a chart...when I created the chart (piechart) the values came out all inacurate. I wanted one to be 8% and it came out 6%. I heard you couold try and fix it using decimal places but that only makes a minor difference. Is there something I dont know about thats fairly simple to do? Hi Andrea If you'ld like to give us an example of your data (e.g. three categories and the associated percentages) and explain to us where the percentages came from - did you type th...

can cell's act like time clock at a certen time/date for payments
i was wondering if one can use a sheet cell of a particuler item, to function like a time clock to remind of schedule payments. i have created a basic family budget planner which has in cridet cards to be paid. You might investigate the use of 'Conditional Formatting' to change the background colour of a cell when today's date (TODAY()) has some meaning in relation to the pay date. eg. Red when overdue, yellow when due in 5 days time, otherwise green. HTH, Fred "Colin2u" wrote: > i was wondering if one can use a sheet cell of a particuler item, to function &g...

Outbound Internet SMTP Mail Sits in Queue for long time.
I have just added an additional recipient policy to accomodate a second domain and I am receiving e-mails just fine from both domains. I am experiencing problems when sending e-mails outside of the organization. The mail will sit in the queue for a long time. If I reboot the server it seems to send the mail off. Any suggestions. I don't recall this being this way before I added the recipient policy. Thanks ...