Formula to convert range of time into 1 hour increment

[Todd]

Hi,

I'm working on a report that I need to convert a range of time into only 1
hour increments. Here is an example.

A1 Need new cell to show
10:19 AM 10 AM
3:54 PM 3 PM
11:23 AM 11 AM
7:44 AM 7 AM

The new cell will not need the minutes, just the hour in which the event
occured. I have over 40,000 cells that need to be converted, so anything
simple would be great.

Thanks!

 

[Bob Phillips]

=INT(A2*24)/24

and copy down

 

[jlclyde]

Hi,

I'm working on a report that I need to convert a range of time into only 1
hour increments.  Here is an example.

A1                   Need new cell to show
10:19 AM         10 AM  
3:54 PM            3 PM
11:23 AM         11 AM
7:44 AM           7 AM

The new cell will not need the minutes, just the hour in which the event
occured.  I have over 40,000 cells that need to be converted, so anything
simple would be great.

Thanks!

Could you convert the format of the cell to custom H? These seems to
drop all minutes from mine.
Thanks,
Jay

 

[T. Valko]

Try this:

=TIME(HOUR(A1),0,0)

Format as h AM/PM

Copy down as needed

 

[Mike H]

Hi,

I dont think it gets much simpler. In B1 enter

=a1
drag down

Format as hh
am/pm

Mike

 

[Todd]

Thanks Bob! I new this had to be simple, but didn't know what to do. 40,000
cells are now converted!