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.

Custom format h:mm AM/PM
--
David Biddulph

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

Chris

This formatted as h:mm AM/PM

=MROUND(A1,1/1440)

Mike

"Chris" wrote:

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.

On Thu, 14 Feb 2008 11:54:01 -0800, Chris
wrote:

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

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


"Ron Rosenfeld" wrote:

On Thu, 14 Feb 2008 11:54:01 -0800, Chris
wrote:

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

On Thu, 14 Feb 2008 12:54:01 -0800, Chris
wrote:

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

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

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

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

Rick



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.

On Thu, 14 Feb 2008 22:22:32 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

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