Results 1 to 5 of 5
Thread: Round times to the NEAREST hour

20140819, 03:31 #1
 Join Date
 Aug 2014
 Posts
 13
 Thanks
 1
 Thanked 0 Times in 0 Posts
Round times to the NEAREST hour
Hi all, me again.
I have googled and tried various methods, however, I can only really find FLOOR() or CEILING() formulas.
I need to avoid the MROUND() function as not all of our machines have a the Data Analysis AddIn.
I don't want to round it down or up, I need the nearest. 10:29 would be 10:00, while 10:31 would be 11:00.
The formula I am trying is below, but it only returns 0 for every single row.
Column J is formatted simply as hh:mm, and contains times only, no dates.
=IF(TEXT(J2,"HH:MM")<"00:30",0,IF(TEXT(J2,"HH:MM") <"01:30",1,IF(TEXT(J2,"HH:MM")<"01:00",1,IF(TEXT(J 2,"HH:MM")<"02:30",2,IF(TEXT(J2,"HH:MM")<"03:30",3 ,IF(TEXT(J2,"HH:MM")<"04:30",4,IF(TEXT(J2,"HH:MM") <"05:30",5,IF(TEXT(J2,"HH:MM")<"06:30",6,IF(TEXT(J 2,"HH:MM")<"07:30",7,IF(TEXT(J2,"HH:MM")<"08:30",8 ,IF(TEXT(J2,"HH:MM")<"09:30",9,IF(TEXT(J2,"HH:MM") <"10:30",10,IF(TEXT(J2,"HH:MM")<"11:30",11,IF(TEXT (J2,"HH:MM")<"12:30",12,IF(TEXT(J2,"HH:MM")<"13:30 ",13,IF(TEXT(J2,"HH:MM")<"14:30",14,IF(TEXT(J2,"HH :MM")<"15:30",15,IF(TEXT(J2,"HH:MM")<"16:30",16,IF (TEXT(J2,"HH:MM")<"17:30",17,IF(TEXT(J2,"HH:MM")<" 18:30",18,IF(TEXT(J2,"HH:MM")<"19:30",19,IF(TEXT(J 2,"HH:MM")<"20:30",20,IF(TEXT(J2,"HH:MM")<"21:30", 21,IF(TEXT(J2,"HH:MM")<"22:30",22,IF(TEXT(J2,"HH:M M")<"23:30",23)))))))))))))))))))))))))
Thanks in advance, guys

Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual  Subscribe and download Chapter 1 for free!

20140819, 03:34 #2
 Join Date
 Aug 2014
 Posts
 13
 Thanks
 1
 Thanked 0 Times in 0 Posts
Doesn't matter guys, I saved the file and closed it. When I reopened it, it was working.
Can this thread be deleted please? I don't want to waste anyones time

20140819, 03:50 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 341 Times in 334 Posts
You can essentially accomplish what you have with your formula with:
=INT(J2*24+0.5)
Steve

20140821, 20:16 #4
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 1,200
 Thanks
 46
 Thanked 228 Times in 211 Posts
If you wish to convert to the nearest hour (time) instead of an integer, use the formula:
=ROUND(A1*24,0)/24
Maud

20140822, 21:36 #5
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 1,200
 Thanks
 46
 Thanked 228 Times in 211 Posts
To round to other intervals, here is the complete formula:
=ROUND(A1*24/x,0)*x/24
where X is:
X=1 round to nearest hour
X=0.5 Round to nearest ½ hour
X=0.25 Round to nearest ¼ hour