Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it Possible?
Is it possible to 'round' a time to the next nearest 15min interval?
As an example in cell A1 I have a value that returns 2:07 PM (its formated as h:mm AM/PM), but in B1 I wish to translate this to the nearest 15min interval in an hour which is 2:15 PM, if the value in A1 was 5:39 PM I would want to show 5:45 PM in B1 etc etc Any guidance appreciated |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it Possible?
Nel post
*John* ha scritto: Is it possible to 'round' a time to the next nearest 15min interval? As an example in cell A1 I have a value that returns 2:07 PM (its formated as h:mm AM/PM), but in B1 I wish to translate this to the nearest 15min interval in an hour which is 2:15 PM, if the value in A1 was 5:39 PM I would want to show 5:45 PM in B1 etc etc Any guidance appreciated Hi John, maybe this can help: =MROUND(E8,15/60/24) but for 2:07 PM the nearest 15 minutes take to 2:00 PM, instead of 2:15 PM, maybe you can "play" with this formula to find the best result... -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it Possible?
Nel post
*Franz Verga* ha scritto: Nel post *John* ha scritto: Is it possible to 'round' a time to the next nearest 15min interval? As an example in cell A1 I have a value that returns 2:07 PM (its formated as h:mm AM/PM), but in B1 I wish to translate this to the nearest 15min interval in an hour which is 2:15 PM, if the value in A1 was 5:39 PM I would want to show 5:45 PM in B1 etc etc Any guidance appreciated Hi John, maybe this can help: =MROUND(E8,15/60/24) but for 2:07 PM the nearest 15 minutes take to 2:00 PM, instead of 2:15 PM, maybe you can "play" with this formula to find the best result... I think I got it: =MROUND(E8+30/60/60/24,15/60/24) obviusly in E8 there is the time to round. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it Possible?
Franz
Thanks for your help, it certainly is nearly there and does round to the nearest 15mins. But I'm looking to get the nearest 'next' 15min. Sounds confusing but say if E8 showed 8:02 PM I'd want my formula to return 8:15 PM and not 8:00 PM But a smart formula you have given nonetheless Thanks "Franz Verga" wrote in message ... Nel post *Franz Verga* ha scritto: Nel post *John* ha scritto: Is it possible to 'round' a time to the next nearest 15min interval? As an example in cell A1 I have a value that returns 2:07 PM (its formated as h:mm AM/PM), but in B1 I wish to translate this to the nearest 15min interval in an hour which is 2:15 PM, if the value in A1 was 5:39 PM I would want to show 5:45 PM in B1 etc etc Any guidance appreciated Hi John, maybe this can help: =MROUND(E8,15/60/24) but for 2:07 PM the nearest 15 minutes take to 2:00 PM, instead of 2:15 PM, maybe you can "play" with this formula to find the best result... I think I got it: =MROUND(E8+30/60/60/24,15/60/24) obviusly in E8 there is the time to round. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it Possible?
Nel post
*John* ha scritto: I think finally I got it. Try this one: =IF(E8-MROUND(E8,15/60/24)0,MROUND(E8,15/60/24)+15/60/24,MROUND(E8,15/60/24)) Franz Thanks for your help, it certainly is nearly there and does round to the nearest 15mins. But I'm looking to get the nearest 'next' 15min. Sounds confusing but say if E8 showed 8:02 PM I'd want my formula to return 8:15 PM and not 8:00 PM But a smart formula you have given nonetheless Thanks "Franz Verga" wrote in message ... Nel post *Franz Verga* ha scritto: Nel post *John* ha scritto: Is it possible to 'round' a time to the next nearest 15min interval? As an example in cell A1 I have a value that returns 2:07 PM (its formated as h:mm AM/PM), but in B1 I wish to translate this to the nearest 15min interval in an hour which is 2:15 PM, if the value in A1 was 5:39 PM I would want to show 5:45 PM in B1 etc etc Any guidance appreciated Hi John, maybe this can help: =MROUND(E8,15/60/24) but for 2:07 PM the nearest 15 minutes take to 2:00 PM, instead of 2:15 PM, maybe you can "play" with this formula to find the best result... I think I got it: =MROUND(E8+30/60/60/24,15/60/24) obviusly in E8 there is the time to round. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy -- (I'm not sure of names of menues, option and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it Possible?
Franz
Your an absolute genius. Many thanks for your determined efforts, its exactly what I require Thanks "Franz Verga" wrote in message ... Nel post *John* ha scritto: I think finally I got it. Try this one: =IF(E8-MROUND(E8,15/60/24)0,MROUND(E8,15/60/24)+15/60/24,MROUND(E8,15/60/24)) Franz Thanks for your help, it certainly is nearly there and does round to the nearest 15mins. But I'm looking to get the nearest 'next' 15min. Sounds confusing but say if E8 showed 8:02 PM I'd want my formula to return 8:15 PM and not 8:00 PM But a smart formula you have given nonetheless Thanks "Franz Verga" wrote in message ... Nel post *Franz Verga* ha scritto: Nel post *John* ha scritto: Is it possible to 'round' a time to the next nearest 15min interval? As an example in cell A1 I have a value that returns 2:07 PM (its formated as h:mm AM/PM), but in B1 I wish to translate this to the nearest 15min interval in an hour which is 2:15 PM, if the value in A1 was 5:39 PM I would want to show 5:45 PM in B1 etc etc Any guidance appreciated Hi John, maybe this can help: =MROUND(E8,15/60/24) but for 2:07 PM the nearest 15 minutes take to 2:00 PM, instead of 2:15 PM, maybe you can "play" with this formula to find the best result... I think I got it: =MROUND(E8+30/60/60/24,15/60/24) obviusly in E8 there is the time to round. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy -- (I'm not sure of names of menues, option and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it Possible?
Franz
One small thing I've noticed is that when my time is exactly on the Qtr hour, say 13:15 this also rounds to the next 15 i.e. 13:30. Is there a way to leave these as 13:15? Just a note if it is 15:31, then this should ound to 15:45 - as it does with your existing formula I just can't tweak your formula to the correct syntax Thanks "Franz Verga" wrote in message ... Nel post *John* ha scritto: I think finally I got it. Try this one: =IF(E8-MROUND(E8,15/60/24)0,MROUND(E8,15/60/24)+15/60/24,MROUND(E8,15/60/24)) Franz Thanks for your help, it certainly is nearly there and does round to the nearest 15mins. But I'm looking to get the nearest 'next' 15min. Sounds confusing but say if E8 showed 8:02 PM I'd want my formula to return 8:15 PM and not 8:00 PM But a smart formula you have given nonetheless Thanks "Franz Verga" wrote in message ... Nel post *Franz Verga* ha scritto: Nel post *John* ha scritto: Is it possible to 'round' a time to the next nearest 15min interval? As an example in cell A1 I have a value that returns 2:07 PM (its formated as h:mm AM/PM), but in B1 I wish to translate this to the nearest 15min interval in an hour which is 2:15 PM, if the value in A1 was 5:39 PM I would want to show 5:45 PM in B1 etc etc Any guidance appreciated Hi John, maybe this can help: =MROUND(E8,15/60/24) but for 2:07 PM the nearest 15 minutes take to 2:00 PM, instead of 2:15 PM, maybe you can "play" with this formula to find the best result... I think I got it: =MROUND(E8+30/60/60/24,15/60/24) obviusly in E8 there is the time to round. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy -- (I'm not sure of names of menues, option and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|