Help with Time Function

  • Thread starter Thread starter Chris
  • Start date Start date
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.
 
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.
Click to expand...


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 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
Click to expand...

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

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

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

B2: =TIME(HOUR(A2),MINUTE(A2)+(SECOND(A2)>=30),0)
Click to expand...

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