Convert Number to Time

[Guest]

I am struggling with some data I exported from an Oracle Aspect DB into
Excel. One column is a list of times in the format: HHMMSS but if there is
no value for hours or minutes, there will be no leading zeros. This means
the value can have anywhere from 1 to 6 digits.

For example, here are some sample times in this column:
ORIG_TIME
1
46
121
404
3008
12342
175632

These would translate into the following times:
12:00:01 AM
12:00:46 AM
12:01:21 AM
12:04:04 AM
12:30:08 AM
01:23:42 AM
05:56:32 PM

I would like a formula/method for converting these numbers into time values
as recognized by Excel. I would be fine with the times in AM/PM or 24 Hour
time. Any assistance would be greatly appreciated.

Thanks!

 

[T. Valko]

One way:

Assume number values are in A1 on down.

Enter this formula in B1 and copy down as needed:

=TIME(MID(A1*0.000001,3,2),MID(A1*0.000001,5,2),MID(A1*0.000001,7,2))

Then format in the TIME style of your choice. I guess the first selection
listed would be your best choice.

 

[Guest]

The solution you proposed worked perfectly! Thank you so much for your help
on this, it will really save me a good amount of time.

Thanks!

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[BenS]

Perhaps someone can help...some months later I've noticed that the formula
below produces errors with some values. These values always seem to end in
zero when the error happens, but not all values that end in zero produce the
error. Here are some examples of values that produce an error with this
formula:

3200
45800
134600
151800
215300
174700

Can anyone suggest a modification or alternative to the formula provided
below that can convert HHMMSS numbers to actual times?

Thanks!

 

[T. Valko]

Is this what those times should be:

3200 = 12:32:00 AM
45800 = 4:58:00 AM
134600 = 1:46:00 PM
151800 = 3:18:00 PM
215300 = 9:53:00 PM
174700 = 5:47:00 PM

If those are the expected results try this:

=--TEXT(A1,"00\:00\:00")

Format as h:mm:ss AM/PM

 

[BenS]

Thank you Biff for the additional information and sorry for my delayed
response. Your suggestion does convert the values below into times. But,
the result of the formula does not respond to date formatting. Also, the
=--TEXT(A1,"00\:00\:00") formula does not return appropriate values when the
time value is anything other than one ending in two zeros.

Is it possible to achieve my original aim with a single formula that
accounts for both scenarios?

Many Thanks,
Ben