Help with Time Function

[Chris]

I have a list of a few thousand times in this 24 hour format:

14:54:12
04:50:46

I need to convert all to 12 hour format rounded to the nearest minute,
example:

14:54:12 would be 2:54 PM
04:50:46 would be 4:51 AM

I am totally lost and dread having to convert thousands by hand, but that's
the exact format the job requires. Any help is greatly appreciated.

 

[David Biddulph]

Custom format h:mm AM/PM

 

[Mike H]

Chris

This formatted as h:mm AM/PM

=MROUND(A1,1/1440)

Mike

 

[Ron Rosenfeld]

I have a list of a few thousand times in this 24 hour format:

14:54:12
04:50:46

I need to convert all to 12 hour format rounded to the nearest minute,
example:

14:54:12 would be 2:54 PM
04:50:46 would be 4:51 AM

I am totally lost and dread having to convert thousands by hand, but that's
the exact format the job requires. Any help is greatly appreciated.

For some reason, Excel does not round times when formatting them, so we have to
add a rounding step to what should be just simple formatting.

Assumption: Your times are properly entered Excel times and are in A2:An

!!BACK UP YOUR DATA!!!!!

B2: =ROUND(A2/TIME(0,1,0),0)*TIME(0,1,0)
Fill down to Bn

Select B2:Bn

Edit/Copy

Select A2

Edit/Paste Special Values

Select A2:An

Format/Cells/Number/Custom Type: h:mm AM/PM

<OK>

And you're done.

Alternatively, if you prefer or need to have the information in Text STrings,
you could use:

B2: =TEXT(ROUND(A2/TIME(0,1,0),0)*TIME(0,1,0),"h:mm AM/PM")

but you would not be able to easily use these in subsequent calculations.
--ron

 

[Chris]

I don't know what else to say except it worked, and I really really really
appreciate it.

 

[Ron Rosenfeld]

I don't know what else to say except it worked, and I really really really
appreciate it.

You're welcome. Thanks for the feedback

--ron

 

[Rick Rothstein \(MVP - VB\)]

I have a list of a few thousand times in this 24 hour format:

For some reason, Excel does not round times when formatting them, so we
have to
add a rounding step to what should be just simple formatting.

Assumption: Your times are properly entered Excel times and are in A2:An

!!BACK UP YOUR DATA!!!!!

B2: =ROUND(A2/TIME(0,1,0),0)*TIME(0,1,0)
Fill down to Bn

Wouldn't this work (it's longer, but seems more straightforward to me)?

B2: =TIME(HOUR(A2),MINUTE(A2)+(SECOND(A2)>=30),0)

Rick

 

[Ron Rosenfeld]

Wouldn't this work (it's longer, but seems more straightforward to me)?

B2: =TIME(HOUR(A2),MINUTE(A2)+(SECOND(A2)>=30),0)

It looks like it should work, also.
--ron