Help with Time Function

C

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.
 
R

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
 
C

Chris

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

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top