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

"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

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),MI D(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.

--
Biff
Microsoft Excel MVP


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