Formula to convert range of time into 1 hour increment
 

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!

Formula to convert range of time into 1 hour increment
 

=INT(A2*24)/24

and copy down

--
__________________________________
HTH

Bob

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

Formula to convert range of time into 1 hour increment
 

On Nov 6, 1:15*pm, Todd wrote:
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

Formula to convert range of time into 1 hour increment
 

Try this:

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

Format as h AM/PM

Copy down as needed

--
Biff
Microsoft Excel MVP


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

Formula to convert range of time into 1 hour increment
 

Hi,

I dont think it gets much simpler. In B1 enter

=a1
drag down

Format as hh
am/pm

Mike

"Todd" wrote:

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!

Formula to convert range of time into 1 hour increment
 

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

"Bob Phillips" wrote:

=INT(A2*24)/24

and copy down

--
__________________________________
HTH

Bob

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