Rounding up Time Values

Hi

I am using the formula below to round time values up,in this case t
the next five minute increment


=(TIME(HOUR(BB14),CEILING(MINUTE(BB14),BB13),0))


BB13 = 5
BB14 = 16:00:02

This formula only rounds up to 16:05:02 if the time is 16:01:00 o
greater,i would prefer it if the formula could make the time round u
as soon as one second has passed, 16:00:01 and not when one minute ha
passed.

Can this be done 

Ad

--
Message posted from http://www.ExcelForum.com

0
5/31/2004 7:55:05 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
544 Views

Similar Articles

[PageSpeed] 12

What is in BB14?
You can just use

=CEILING(BB14,5/1440)

will do what you want

Or if there are dates as well you can just format as time or remove the date

=CEILING(MOD(BB14,1),5/1440)

-- 


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

"ADE2 >" <<ADE2.174z1r@excelforum-nospam.com> wrote in message
news:ADE2.174z1r@excelforum-nospam.com...
> Hi
>
> I am using the formula below to round time values up,in this case to
> the next five minute increment
>
>
> =(TIME(HOUR(BB14),CEILING(MINUTE(BB14),BB13),0))
>
>
> BB13 = 5
> BB14 = 16:00:02
>
> This formula only rounds up to 16:05:02 if the time is 16:01:00 or
> greater,i would prefer it if the formula could make the time round up
> as soon as one second has passed, 16:00:01 and not when one minute has
> passed.
>
> Can this be done
>
> Ade
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
terre081 (3244)
5/31/2004 8:17:27 PM
Ade,

Is this what you want,

=ROUNDUP(BB14*24*(60/BB13),0)/24/(60/BB13)

or

=CEILING(BB14*24*(60/BB13),1)/24/(60/BB13)

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ADE2 >" <<ADE2.174z1r@excelforum-nospam.com> wrote in message
news:ADE2.174z1r@excelforum-nospam.com...
> Hi
>
> I am using the formula below to round time values up,in this case to
> the next five minute increment
>
>
> =(TIME(HOUR(BB14),CEILING(MINUTE(BB14),BB13),0))
>
>
> BB13 = 5
> BB14 = 16:00:02
>
> This formula only rounds up to 16:05:02 if the time is 16:01:00 or
> greater,i would prefer it if the formula could make the time round up
> as soon as one second has passed, 16:00:01 and not when one minute has
> passed.
>
> Can this be done
>
> Ade
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
5/31/2004 8:19:01 PM
I have used the following formula to round up the time in cell BB3 t
the nearest five minutes.

=CEILING(BB3,5/1440)

this rounds up 11:20:01  to 11:25:00

I now need to round up to the nearest four hour increments

round up at            desired value
00:00:01               04:00:00
04:00:01               08:00:00
08:00:01               12:00:00
12:00:01               16:00:00
16:00:01               20:00:00
20:00:01               00:00:00

So i only want it to round up 6 times per day at the times shown in th
left column(at one second past the four hour period)

Not sure how to approach this


Thanks for the help


Ad

--
Message posted from http://www.ExcelForum.com

0
5/31/2004 11:24:10 PM
One way

=CEILING(BB3,4/24)

-- 


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

"ADE2 >" <<ADE2.1758q8@excelforum-nospam.com> wrote in message
news:ADE2.1758q8@excelforum-nospam.com...
> I have used the following formula to round up the time in cell BB3 to
> the nearest five minutes.
>
> =CEILING(BB3,5/1440)
>
> this rounds up 11:20:01  to 11:25:00
>
> I now need to round up to the nearest four hour increments
>
> round up at            desired value
> 00:00:01               04:00:00
> 04:00:01               08:00:00
> 08:00:01               12:00:00
> 12:00:01               16:00:00
> 16:00:01               20:00:00
> 20:00:01               00:00:00
>
> So i only want it to round up 6 times per day at the times shown in the
> left column(at one second past the four hour period)
>
> Not sure how to approach this
>
>
> Thanks for the help
>
>
> Ade
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
terre081 (3244)
6/1/2004 1:15:32 AM
Thanks Peo, that has done the trick

Ad

--
Message posted from http://www.ExcelForum.com

0
6/1/2004 10:00:20 AM
My formula was based upon your original post and assumed the rounding
minutes factor in BB13. So you could have put 240 in there and it would have
worked.

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ADE2 >" <<ADE2.1758q8@excelforum-nospam.com> wrote in message
news:ADE2.1758q8@excelforum-nospam.com...
> I have used the following formula to round up the time in cell BB3 to
> the nearest five minutes.
>
> =CEILING(BB3,5/1440)
>
> this rounds up 11:20:01  to 11:25:00
>
> I now need to round up to the nearest four hour increments
>
> round up at            desired value
> 00:00:01               04:00:00
> 04:00:01               08:00:00
> 08:00:01               12:00:00
> 12:00:01               16:00:00
> 16:00:01               20:00:00
> 20:00:01               00:00:00
>
> So i only want it to round up 6 times per day at the times shown in the
> left column(at one second past the four hour period)
>
> Not sure how to approach this
>
>
> Thanks for the help
>
>
> Ade
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
6/1/2004 12:03:19 PM
Reply:

Similar Artilces:

How do you get the attribute value using XPath in VB.Net 2003?
Hi, How do you get the attribute value using XPath in VB.Net 2003? Many thanks, aushknotes "aushknotes" <aushknotes@discussions.microsoft.com> wrote in message news:508426F2-1C8A-4AD2-A52E-B80B9798AC0C@microsoft.com... > Hi, > > How do you get the attribute value using XPath in VB.Net 2003? > Prefix @ to the name of the attribute value. XmlAttribute attrib = (XmlAttribute)dom.selectSingleNode("/path/@attributeName"); -- Anthony Jones - MVP ASP/ASP.NET aushknotes wrote: > How do you get the attribute value using XPath in VB.Net 2003? ...

How to change the string value in the registry?
How to change the "string value" in the registry from the code? As there is a functionality in my application that the user can change the "string value" with some GUI. But I m not able to change it as there is a function RegReplaceKey but its only to take the backup of old file and to replace it with new one and even i dont know how to use this functin to suit my case. RegSetValue/Ex() should do it I think. -Seetharam Look at the Registry APIs. Also, take a look at my Registry class on my MVP Tips site. joe On Sun, 26 Aug 2007 23:59:33 -0700, HItz <hitesh_im...

Getting rid of #value
How am I able to add a column of numbers when one of the numbers in the formula is itself a formula that results in #VALUE (because that number the product of zero times another number)? Thanks First, the product of zero and another number shouldn't be giving you #VALUE!. Your best bet is to fix or bypass whatever is causing the #VALUE! error. If you can't do that for some reason, array-enter (CTRL-SHIFT-ENTER or CMD-RETURN): =SUM(IF(ISERROR(A1:A20),"",A1:A20)) In article <_e6dnZvUCo7b1mHdRVn-sw@comcast.com>, "Craig" <jcstone1@comcast.net>...

'Counter' value of the 'Perflib' subkey
I have a program which queries the 'Counter' value of the 'Perflib' subkey in the Windows registry using RegQueryValueEx. It used to work fine but now the debug build of my program is broken on Windows 2008 Server R2. The 'Counter' value is a REG_MULTI_SZ type data and is terminated with two NULL characters. However, I found out for Windows 2008 Server R2, the value returned from RegQueryValueEx has an extra character(0xcdcd) after the two NULL characters, which ends the 'Counter' string value. And that extra character causes my program to fail ...

Behind the times
Behind the times, I know. I have recently graduated from Office 2000 to 2003. In 2000, when I would insert a picture I could format it with one or more items (line, line color, position, etc) and then move on to the next jpg, hit the F4 key and the photo would have the same formatting. That doesn't seem to work in 2003. Help files discuss "repeat" on the edit menu, but it doesn't seem to work on a pic. What am I missing here? This is going to be a pain to format each picture individually. I don't have 2003 handy to check, so I'm not sure what t...

screen freeze after long time running
Hi I have a program written in Microsoft Visual C++ .NET . Its interface is based on CFormView. It includes three tabs to show information and plots. The plots are made by BitBlt() which copies data from the vectors to the screen around every 5 minutes. The problem is that after the program runs continuously for more than 3 days the screen freezes. Nothing is updated. I have to minimize the window and it may return to normal look. Some static labels on the interface also freeze or appear strange look. Does anyone have similar experience ? Thank you for your hints or suggestions! <e...

retrive value from lookup screen
hi, i hv small problem i make refrence for smartlist (SL)in GP form to enable me open the SL lookup form ,and i add lookup button to open that smart list but i want the retive value from SL to be inserted in text box exist on GP form please help me ...

Adding Hyperlink to multiple values within a cell
My spreadsheet contains a list of people. The cell next to each nam contains multiple numeric values for identifying a specific piece o information. I would like to be able to click on one of those number (value) and a comment window pop up with the information associate with it, or be hyperlinked to the information further down th speadsheet. I want to avoid using multiple cells for this. Is this possible? Thank -- t2tru ----------------------------------------------------------------------- t2true's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=387 View this...

automatically sending messages at set times??
Wondering if there's a way with Outlook to send a default message at a specified time?? ie: a message for my ex to repay me her debts on a weekly basis :P "Adam Membrey" <membreya@hotmail.com> wrote in message news:O$2Z4y0zEHA.3416@TK2MSFTNGP09.phx.gbl... > Wondering if there's a way with Outlook to send a default message at a > specified time?? > > ie: a message for my ex to repay me her debts on a weekly basis :P Sure if you are willing to write code but chances are it would be easier to do from the command line with one of the email utilities like ...

can i see the date the last time a cell was changed?
I am trying to figure out a formulla to make the date appear in one cell everytime anouther cell's data is chaged. Use a worksheet_change event to copy the cell address and put in a date stamp. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "JohnNuTek" <JohnNuTek@discussions.microsoft.com> wrote in message news:5085A544-CBF4-4B81-A244-B03DE9A0E9E6@microsoft.com... >I am trying to figure out a formulla to make the date appear in one cell > everytime anouther cell's data is chaged. There really isn't a worksheet formula to do that. Typicall...

Open excel sheet twice at same time?
Hi, Probably a question asked all the time, but I often have trouble opening a file after making a desktop shortcut to an excel file. When opening, it gives an error message about opening a file with the same name at the same time (even though I'm not). The error message continues by saying that it doesn't matter if the files are in different folders and suggests renaming one of the files. Any ideas? Thanks. Robert No reason I can think of that would cause Excel to load the file twice irrespective of launching it from a shortcut. Couple of things to check: - The file isn'...

CLick on Box to enter time of day
I have a worksheet where payment is entered in column C. I would like to use Column B as a time stamp. In other words as soon as data is entered in C5 then the exact time comes up in B5. When data is entered in C6 then the exact time cones up in C5. I do not want this time to change even when the page is saved and opened later on. Elfego You can enter a static time in a cell by hitting CTRL + SHIFT + ;(semi-colon) You could also use event code to enter a static date when you enter something in a cell. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in ...

Time entered (and handled as data) in simple minutes and seconds.
Using Excel 2003. How can I get Excel to display and handle entries for duration in minutes and seconds only (without having to enter a 4'30" entry as 0:04:30)? I have gotten as far as getting it to display as "04:30.0", but all I need is single digit minutes (data on this spreadsheet will never go over 9:59) and double digit seconds (and to be able to enter them simply as in "4:30"). I couldn't find that as an option in the ones listed for time in the cell format dialogue box. Right now I have to enter 0:04:30 in order to get it to work right. Is t...

Combo values from query based on form fields
I am setting the values for a combo box in a form(s) via a query that 'filters' the results with criteria based upon the values of other fields on the form. The combo is a field that is bound. However, this is giving all kinds of problems ranging from Access completely crashing to being asked for the parameter values of those criteria fields when closing the form. I have tried making the combo an unbound field and then setting the value of the bound field to that unbound field after update, but that still leads to the same issues. How can I do this? As example - I have a form w...

Microsoft Business Portal Project Time and Expense Software Compon
I'm interested in the time and expense functionality (called "Business Portal Project Time and Expense") that is described under "Dynamics GP" domain. However, I do not readily see the relationship between "Dynamics GP" and "Microsoft Business Portal Project Time and Expense." What Microsoft products do I need in order to reach my goal of operating "Business Portal Project Time and Expense"? Many thanks. In terms of what you need to purchase, the answer depends upon what you have an what licensing method you are using: The Time and...

Add values in a column according to value in another column
How can I add the values in a column according to values in another column? If there is any value in a row in column B, I want to include the value of the corresponding row in column A. I'm flexible as to whether this is ANY value (i.e. not empty) or greater than zero. Hi Paul Maybe something like this =IF(B1="","",IF(B1>0,B1+A1)) Regards Cimjet "Paul Kaye" <paulmjkaye@gmail.com> wrote in message news:05befaf3-9ba8-48c8-aebb-654f0269d1dc@34g2000hsf.googlegroups.com... > How can I add the values in a column according to values in another > colu...

CComboBox shows only one item at a time !
Hi All, I am using CComboBox control and have tried both dropdown and dropddown list styles. The problem is that I have several items in the combo box but dropdown shows only 1 at a time (i.e. the arrows of v-scrollbar are stuck together).. How do I remedy this ? Thanks in advance. Ashish Got it... Thanks "Ashish" <abc@def.com> wrote in message news:eqRb$LSyDHA.2304@TK2MSFTNGP12.phx.gbl... > Hi All, > I am using CComboBox control and have tried both dropdown and dropddown list > styles. The problem is that I have several items in the combo box but > dropdown sho...

IS Mailbox Average Delivery Time
We are using HP Open View and are receiving messages stating: “The IS Mailbox database instance 'SG2-Front-House'Average Delivery time (13228 secs) for last 10 messages is too high (>=10 secs)” We receive an alert approximately 4 times an hour with the seconds ranging anywhere from 100 to 30,000. We had restriction set on a SMTP connector at one point. We removed these to see if this was the cause. Nothing has changed. I have looked online, but can’t find much information as to what might cause this. One theory I was leaning towards was the fact that we have some users w...

lookup value based on multiple criteria
Banging my head on this one... can anyone help?? I've got several thousand rows of data in three columns, structured similar to example below. Each set of ID numbers represents a separate contact entry (person) in an address book. FIELDNAME could include one or more of about 200 fields, and VALUE may be blank. ID FIELDNAME VALUE 1 FirstName Bob 1 LastName Smith 1 Company Tech Smith, Inc. 2 LastName Johnson 2 Company <blank> 2 FirstName Jim I've got a second sheet set up with all of the 200 possible FIEL...

List records that do not include specified value
I have a very basic select query that list all the records from a table SELECT tbl_cngu.cngu_no, tbl_cngu_recalls.recall_no FROM tbl_cngu LEFT JOIN tbl_cngu_recalls ON tbl_cngu.cngu_no = tbl_cngu_recalls.cngu_no ORDER BY tbl_cngu.cngu_no, tbl_cngu_recalls.recall_no DESC; Now I need to apply a twist and do no know how. The [recall_no] field is a numeric field 1,2,3,4,5,... and what I would like to do is supply the query with a [recall_no] value and have the query return those records for which there is no such [recall_no] for a each given [cngu_no]. How would I do this? T...

Difference between two times in seconds
hi i need a favour regarding finding out the time spent between two times in seconds. I need the current time like 12:35:45 PM in a cell A1 and after some time put time in cell A2 as 12:37:50 PM. I need the shortcut formula for putting in a cells (A1&A2) so that it should come in Hr: Min: Sec format. And after that I need the difference of this two times in Seconds (125 seconds above) in cell A3. hi use a custom format in A3. [ss] this will keep the time from rolling over to minutes and total the seconds. regards FSt1 "Radhakant Panigrahi" wrote: ...

Copy Macro values to new sheet
I have a macro which creates a text file to an new excel document. I have placed within this macro a function to put a button on this sheet, the thing is, is that I can't find away to import the code needed for this button to the sheet If you know, can you help me please -- Message posted via http://www.officekb.com ...

time formula #2
I am trying to calculate in and out time for a time sheet spreadsheet. Example: 9:00 am (in time), 5:00 pm (out time). I devised a formula: =IF(D7<C7,((C7-D7)*24)+((C7+D7)*24)*2,(D7-C7)*24) It works for all senarios except if you begin with PM and end with AM. Example: 5:00 pm (in time), 1:00 am (out time) Can anyone help me!! I've done something similar for Surgery in and out time for my hospital. Our times never cross a day though. Understand that time is saved as a faction of a day: a date/time raw looks like 1000.0001 everything to the left of the decimal is date info, e...

Average time from date column?
I have a column that is in general date format, 6/19/2007 5:34:23 PM. In another column I have a value between 10 and around 50. What I have to do is get the average of this number by time in a day. So if the number is 10 all day, then the average is 10. If the number is changed during the day, say its 5 for half the day then 10 for the other half of the day, then the number I'm looking for is 7.5. If the number is 5 for 1/4 of the day and 10 for the other 3/4 of the day then the number would be around 8.5..... if you follow what I'm trying to say. So far I have been a...

time adding in excell
according to the help in Excell-2000 it says to add time use this formula if the result is to be over 24 hours--- =Sum(cell:cell)*24 the problem is it doesnt work --the only sum it comes up with is 0:00 Don, The problem here is that by multiplying the sum by 24, you are changing to a decimal number of hours, but because you are summing time, the result cell format defaults to time. Solution, format the result cell as number, or leave it as time and don't multiply by 24. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the emai...