Round off time to the Nearest Hour

[chiswickbridge]

My work involves Time in dd:mm:yyy hh:mm format and then rounding off the time to the


nearest previous hour.


So if the time is 30 Nov 2016 12:33 hrs, it must show as 30 Nov 2016 12:00 hrs
Similarly the time is 30 Nov 2016 16:49 hrs, it must show as 30 Nov 2016 16:00 hrs


Always rounded off to the hour before the event


For Example :


30 Nov 2016 12:33 in numerical format is 42704.522, this must be reduced to 42704.50


30 Nov 2016 18:33 in numerical format is 42704.775, this must be reduced to 42704.75


So, I need a Formula to round off time to the nearest previous hour.

 

[FDibbins]

Try this...
=FLOOR(A1,"1:00")

 

[Momentman]

Maybe this

Excel 2010
	A	B
1	30/11/2016 12:33	30/11/2016 12:00
2	30/11/2016 18:33	30/11/2016 18:00
3	30/11/2016 16:49	30/11/2016 16:00
Sheet7

Cell Formulas
Range	Formula
B1	=INT(24*(A1-INT(A1)))/24+INT(A1)

 

[chiswickbridge]

Thanks FDibbins and Momentman.... both the formulas work... Good work....Thanks again...

 

[FDibbins]

Good stuff, happy to help