Round down date / time field to nearest hour only

[surkdidat]

Hi all

In A5, I have a date/timestamp , for example "08/11/2016 00:51:33", I need a formula please to extract the time part only, and then round it DOWN to the nearest hour

30/10/2016 20:49:17	20:00
29/10/2016 10:58:23	10:00

29/10/2016 03:07:05	03:00
07/07/2016 14:06:25	14:00

Many thanks!

 

[Phuoc]

Try this

=TIME(HOUR(A5),0,0)

 

[surkdidat]

Try this

=TIME(HOUR(A5),0,0)

Thanks - Is there a possible way of doing this so the seconds column is not displayed? (Data is being transferred over into a PIvot Table)