Rounding Time values

[mebsmith]

I am looking to round a time field such as this:

6:40:00 AM

to the nearest hour, keeping it in a format so that it can be displayed in
24hr format for easy identification as to the time of day. Even keeping the
AM/PM would be fine too.

Any suggestions? I have exhausted all the ones i thought would work.

Thanks

 

[Brad]

Heres a function to calculate and display results, replace A1 with whatever
cell your time value is in.

=TIME(IF(MINUTE(A1)>30, HOUR(A1)+1,HOUR(A1)),0,0)

 

[John C]

=ROUND(A1*24,0)/24
This assumes A1 is a time only (no time/date).

Hope this helps.

 

[T. Valko]

Try this:

A1 = 6:40:00 AM

=ROUND(A1*24,0)/24

Result = 7:00:00 AM

 

[mebsmith]

Thanks for the help everyone!!

 

[David Biddulph]

=MROUND(A2,1/24) and format with a time format which suits your
requirements.
Look up MROUND in Excel help.

 

[John C]

*sigh*
--
John C

=ROUND(A1*24,0)/24
This assumes A1 is a time only (no time/date).

Hope this helps.

 

[T. Valko]

You're welcome!