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

20140819, 03:31 #1
 Join Date
 Aug 2014
 Posts
 36
 Thanks
 14
 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

20140819, 03:34 #2
 Join Date
 Aug 2014
 Posts
 36
 Thanks
 14
 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 342 Times in 335 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
 3,039
 Thanks
 166
 Thanked 800 Times in 729 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
 3,039
 Thanks
 166
 Thanked 800 Times in 729 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