Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round time to quarter hour
Good afternoon --
I'm having problems setting up a time sheet template, which requires rounding the employee's time to the nearest quarter hour. Here's what I've got: A B 1 7:12 AM =MROUND(A1,0.010417) The 0.010417 number I believe to be the decimal value for 15 minutes. At any rate, the resulting figure in cell B1 is 7:17 AM, instead of 7:15 AM like I want. What am I doing wrong? Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round time to quarter hour
You may need to recheck your typing, or your initial value.
To be accurate you should use 1/96 instead of 0.010417, as the latter has some rounding errors from the true value of 0.0104166666666... (with the 6s continuing to infinity), but the 0.010417 will give 7:15 AM if A1 is simply 7:12 AM. Why don't you reread your formula, and see whether you've used =MROUND(A1,0.01047) instead of =MROUND(A1,0.010417)? The other thing which might exaggerate the rounding error if you have got 0.010417 would be if A1 isn't just a time but contains date and time, merely formatted as time. -- David Biddulph "pdberger" wrote in message ... Good afternoon -- I'm having problems setting up a time sheet template, which requires rounding the employee's time to the nearest quarter hour. Here's what I've got: A B 1 7:12 AM =MROUND(A1,0.010417) The 0.010417 number I believe to be the decimal value for 15 minutes. At any rate, the resulting figure in cell B1 is 7:17 AM, instead of 7:15 AM like I want. What am I doing wrong? Thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round time to quarter hour
=ROUND(A1*96,0)/96
or =MROUND(A1,1/96) format as h:mm "pdberger" wrote: Good afternoon -- I'm having problems setting up a time sheet template, which requires rounding the employee's time to the nearest quarter hour. Here's what I've got: A B 1 7:12 AM =MROUND(A1,0.010417) The 0.010417 number I believe to be the decimal value for 15 minutes. At any rate, the resulting figure in cell B1 is 7:17 AM, instead of 7:15 AM like I want. What am I doing wrong? Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round time to quarter hour
=IF(A1-FLOOR(A1,0.01041666667)CEILING(A1,0.01041666667)-A1,CEILING(A1,0.01041666667),FLOOR(A1,0.0104166666 7))
The above formula will round to the NEAREST quarter hour. If you only want to round up, use =ceiling(a1,0.01041666667). If you only want to round down, use =floor(a1,0.01041666667) -- JNW "pdberger" wrote: Good afternoon -- I'm having problems setting up a time sheet template, which requires rounding the employee's time to the nearest quarter hour. Here's what I've got: A B 1 7:12 AM =MROUND(A1,0.010417) The 0.010417 number I believe to be the decimal value for 15 minutes. At any rate, the resulting figure in cell B1 is 7:17 AM, instead of 7:15 AM like I want. What am I doing wrong? Thanks in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round time to quarter hour
You may need to recheck your typing, or your initial value.
To be accurate you should use 1/96 instead of 0.010417, as the latter has some rounding errors from the true value of 0.0104166666666... (with the 6s continuing to infinity) Or, instead of having to calculate divisors, you can let Excel do all the work... =MROUND(A1,TIME(0,15,0)) Rick |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round time to quarter hour
When I tried this I got only "#NAME?" I also want to round UP to the nearest
15 minutes. I usually have a column C with Minutes that I enter by hand and the next column D would be MINUTES rounded up to the nearest 15 minutes. Please advise how to do this? Thanks. Amy "Rick Rothstein (MVP - VB)" wrote: You may need to recheck your typing, or your initial value. To be accurate you should use 1/96 instead of 0.010417, as the latter has some rounding errors from the true value of 0.0104166666666... (with the 6s continuing to infinity) Or, instead of having to calculate divisors, you can let Excel do all the work... =MROUND(A1,TIME(0,15,0)) Rick |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round time to quarter hour
Assuming you enter the times like this:
7:22 = 7 minutes 22 seconds Try this: =ROUNDUP(A1/(1/96),0)*(1/96) Format as mm:ss -- Biff Microsoft Excel MVP "Amy" wrote in message ... When I tried this I got only "#NAME?" I also want to round UP to the nearest 15 minutes. I usually have a column C with Minutes that I enter by hand and the next column D would be MINUTES rounded up to the nearest 15 minutes. Please advise how to do this? Thanks. Amy "Rick Rothstein (MVP - VB)" wrote: You may need to recheck your typing, or your initial value. To be accurate you should use 1/96 instead of 0.010417, as the latter has some rounding errors from the true value of 0.0104166666666... (with the 6s continuing to infinity) Or, instead of having to calculate divisors, you can let Excel do all the work... =MROUND(A1,TIME(0,15,0)) Rick |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round time to quarter hour
Another one:
=CEILING(A1,1/96) Format as mm:ss -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Assuming you enter the times like this: 7:22 = 7 minutes 22 seconds Try this: =ROUNDUP(A1/(1/96),0)*(1/96) Format as mm:ss -- Biff Microsoft Excel MVP "Amy" wrote in message ... When I tried this I got only "#NAME?" I also want to round UP to the nearest 15 minutes. I usually have a column C with Minutes that I enter by hand and the next column D would be MINUTES rounded up to the nearest 15 minutes. Please advise how to do this? Thanks. Amy "Rick Rothstein (MVP - VB)" wrote: You may need to recheck your typing, or your initial value. To be accurate you should use 1/96 instead of 0.010417, as the latter has some rounding errors from the true value of 0.0104166666666... (with the 6s continuing to infinity) Or, instead of having to calculate divisors, you can let Excel do all the work... =MROUND(A1,TIME(0,15,0)) Rick |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round time to quarter hour
So when you got a #NAME response from the MROUND function, did you look up
MROUND in Excel help? If you want to round up, you probably want to look at the CEILING function. -- David Biddulph "Amy" wrote in message ... When I tried this I got only "#NAME?" I also want to round UP to the nearest 15 minutes. I usually have a column C with Minutes that I enter by hand and the next column D would be MINUTES rounded up to the nearest 15 minutes. Please advise how to do this? Thanks. Amy "Rick Rothstein (MVP - VB)" wrote: You may need to recheck your typing, or your initial value. To be accurate you should use 1/96 instead of 0.010417, as the latter has some rounding errors from the true value of 0.0104166666666... (with the 6s continuing to infinity) Or, instead of having to calculate divisors, you can let Excel do all the work... =MROUND(A1,TIME(0,15,0)) Rick |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round time to quarter hour
When I tried this I got only "#NAME?"
I'm not sure why you would have gotten that error... the formula I posted works fine in XL2003 and XL2007. I also want to round UP to the nearest 15 minutes. Which you realize you did not say in your original posting, right? This formula should do what you are now asking for... =CEILING(A1,TIME(0,15,0)) Rick |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round time to quarter hour
Analysis ToolPak, Rick?
-- David Biddulph "Rick Rothstein (MVP - VB)" wrote in message ... When I tried this I got only "#NAME?" I'm not sure why you would have gotten that error... the formula I posted works fine in XL2003 and XL2007. .... |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round time to quarter hour
I figured that Add-In had to already be checked given, according to the OP's
original posting, that he used the MROUND function originally and had gotten a numerical result from having done so. Rick "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Analysis ToolPak, Rick? -- David Biddulph "Rick Rothstein (MVP - VB)" wrote in message ... When I tried this I got only "#NAME?" I'm not sure why you would have gotten that error... the formula I posted works fine in XL2003 and XL2007. ... |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round time to quarter hour
The confusion may have arisen in that Amy (to whom you were replying) was
not the OP, but came along to the thread a couple of months later. The OP (pdberger) had MROUND working, but Amy probably didn't. The OP was presumably happy with the answers which you and I had given in September. My guess is that Amy didn't have the ToolPak enabled, but she may possibly have suffered from the occasional glitch where Excel forgets that the ToolPak is supposed to be there. In the latter case, either a disable/ re-enable ToolPak, or a shutdown/ re-open Excel, or a reboot, will usually do the trick. -- David Biddulph "Rick Rothstein (MVP - VB)" wrote in message ... I figured that Add-In had to already be checked given, according to the OP's original posting, that he used the MROUND function originally and had gotten a numerical result from having done so. Rick "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Analysis ToolPak, Rick? -- David Biddulph "Rick Rothstein (MVP - VB)" wrote in message ... When I tried this I got only "#NAME?" I'm not sure why you would have gotten that error... the formula I posted works fine in XL2003 and XL2007. ... |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round time to quarter hour
<<Imagine the sound of a hand slapping a forehead rather hard
You pegged it! I completely missed the name shift as well as the date switch. Whose bright idea in Microsoft was it to append messages in Outlook Express (and now Windows Mail) onto old, existing grouped message threads just because they had the same Subject line? Couldn't they have given us a configurable option to select an "it's too old" time period after which same-subject messages would become a new thread instead of a continuation of an old one? Well, anyway, I was completely fooled on this one... thanks for pointing out the person/date time shift so I could see what I had missed in Amy's response. Rick "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... The confusion may have arisen in that Amy (to whom you were replying) was not the OP, but came along to the thread a couple of months later. The OP (pdberger) had MROUND working, but Amy probably didn't. The OP was presumably happy with the answers which you and I had given in September. My guess is that Amy didn't have the ToolPak enabled, but she may possibly have suffered from the occasional glitch where Excel forgets that the ToolPak is supposed to be there. In the latter case, either a disable/ re-enable ToolPak, or a shutdown/ re-open Excel, or a reboot, will usually do the trick. -- David Biddulph "Rick Rothstein (MVP - VB)" wrote in message ... I figured that Add-In had to already be checked given, according to the OP's original posting, that he used the MROUND function originally and had gotten a numerical result from having done so. Rick "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Analysis ToolPak, Rick? -- David Biddulph "Rick Rothstein (MVP - VB)" wrote in message ... When I tried this I got only "#NAME?" I'm not sure why you would have gotten that error... the formula I posted works fine in XL2003 and XL2007. ... |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round time to quarter hour
In this particular case, Amy had actually replied to your previous message,
so her message had the right reference line in the header to be included in the old thread, but I agree that the problem of identically named threads being clumped by OE is a pain in the proverbials. -- David Biddulph "Rick Rothstein (MVP - VB)" wrote in message ... <<Imagine the sound of a hand slapping a forehead rather hard You pegged it! I completely missed the name shift as well as the date switch. Whose bright idea in Microsoft was it to append messages in Outlook Express (and now Windows Mail) onto old, existing grouped message threads just because they had the same Subject line? Couldn't they have given us a configurable option to select an "it's too old" time period after which same-subject messages would become a new thread instead of a continuation of an old one? Well, anyway, I was completely fooled on this one... thanks for pointing out the person/date time shift so I could see what I had missed in Amy's response. Rick "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... The confusion may have arisen in that Amy (to whom you were replying) was not the OP, but came along to the thread a couple of months later. The OP (pdberger) had MROUND working, but Amy probably didn't. The OP was presumably happy with the answers which you and I had given in September. My guess is that Amy didn't have the ToolPak enabled, but she may possibly have suffered from the occasional glitch where Excel forgets that the ToolPak is supposed to be there. In the latter case, either a disable/ re-enable ToolPak, or a shutdown/ re-open Excel, or a reboot, will usually do the trick. -- David Biddulph "Rick Rothstein (MVP - VB)" wrote in message ... I figured that Add-In had to already be checked given, according to the OP's original posting, that he used the MROUND function originally and had gotten a numerical result from having done so. Rick "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Analysis ToolPak, Rick? -- David Biddulph "Rick Rothstein (MVP - VB)" wrote in message ... When I tried this I got only "#NAME?" I'm not sure why you would have gotten that error... the formula I posted works fine in XL2003 and XL2007. ... |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round time to quarter hour
You are right.... Amy was replying directly to the theme of the old thread
(but the way she worded her message made it sound like the OP following up to my reply). Okay, so starting a new thread after a user-settable time interval is not the way to go. Perhaps, then, a "flag" column (like the Attachments or Watch/Ignore ones) that the user can choose to view (or not) in which a flag-symbol of some kind would be displayed if the message was more than a user-settable time interval older than the message it replied to is the way to go. That way (in this case), I would have been able to see the flag, which would have warned me that message was a delayed follow-up; then I would have been alerted to look at the message in the correct way. Rick "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... In this particular case, Amy had actually replied to your previous message, so her message had the right reference line in the header to be included in the old thread, but I agree that the problem of identically named threads being clumped by OE is a pain in the proverbials. -- David Biddulph "Rick Rothstein (MVP - VB)" wrote in message ... <<Imagine the sound of a hand slapping a forehead rather hard You pegged it! I completely missed the name shift as well as the date switch. Whose bright idea in Microsoft was it to append messages in Outlook Express (and now Windows Mail) onto old, existing grouped message threads just because they had the same Subject line? Couldn't they have given us a configurable option to select an "it's too old" time period after which same-subject messages would become a new thread instead of a continuation of an old one? Well, anyway, I was completely fooled on this one... thanks for pointing out the person/date time shift so I could see what I had missed in Amy's response. Rick "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... The confusion may have arisen in that Amy (to whom you were replying) was not the OP, but came along to the thread a couple of months later. The OP (pdberger) had MROUND working, but Amy probably didn't. The OP was presumably happy with the answers which you and I had given in September. My guess is that Amy didn't have the ToolPak enabled, but she may possibly have suffered from the occasional glitch where Excel forgets that the ToolPak is supposed to be there. In the latter case, either a disable/ re-enable ToolPak, or a shutdown/ re-open Excel, or a reboot, will usually do the trick. -- David Biddulph "Rick Rothstein (MVP - VB)" wrote in message ... I figured that Add-In had to already be checked given, according to the OP's original posting, that he used the MROUND function originally and had gotten a numerical result from having done so. Rick "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Analysis ToolPak, Rick? -- David Biddulph "Rick Rothstein (MVP - VB)" wrote in message ... When I tried this I got only "#NAME?" I'm not sure why you would have gotten that error... the formula I posted works fine in XL2003 and XL2007. ... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I round time to the nearest quarter of an hour | New Users to Excel | |||
how to calculate time start & time finish in quarter hour | Excel Discussion (Misc queries) | |||
rounding up time to quarter hour increments | Excel Worksheet Functions | |||
rounding up time to quarter hour increments | Excel Worksheet Functions | |||
Need to round the time to the nearest quarter hour. Help | Excel Discussion (Misc queries) |