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

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)

"mebsmith" wrote:

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

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

Hope this helps.
--
John C


"mebsmith" wrote:

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

Try this:

A1 = 6:40:00 AM

=ROUND(A1*24,0)/24

Result = 7:00:00 AM

--
Biff
Microsoft Excel MVP


"mebsmith" wrote in message
...
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

Thanks for the help everyone!!

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

"mebsmith" wrote in message
...
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

*sigh*
--
John C


"John C" wrote:

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

Hope this helps.
--
John C


"mebsmith" wrote:

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

You're welcome!

--
Biff
Microsoft Excel MVP


"mebsmith" wrote in message
...
Thanks for the help everyone!!