Convert integer to time query

[Bryony]

Hi, I've exported data from different software which only recognises integers
and now need to convert these values to time e.g. 950 to 09:50 and 1000 to
10:00. I've tried a few formulas with no success, the closest I got was
using left, right and mid functions but this doesn't seem to work for the
complete dataset as the number of characters switches between 3 and 4. I'd
be very grateful for your advice on how to convert integers to time, cheers.

 

[bj]

try
=time(int(val/100),mod(val,100),0)

 

[Toppers]

try:

=TIME(LEFT(D1,LEN(D1)-2),RIGHT(D1,2),0)

 

[T. Valko]

Is 950 9:50 AM or 9:50 PM?

Assuming these "times" are based on a 24hr clock and are either 3 or 4
characters in length:

950 = 9:50 AM
2150 = 9:50 PM

=TIME(LEFT(A1,IF(LEN(A1)=3,1,2)),RIGHT(A1,2),0)

I'm also assuming that you might have entries like 0030 (12:30 AM) and these
entries are formatted as TEXT (otherwise, as a number, Excel doesn't see the
leading 0s).

Biff

 

[Dave Peterson]

You could use a formula like:
=--TEXT(A1,"00\:00\:00")
if 950 represents 0 hours, 9 minutes, 50 seconds

or
=--TEXT(A1,"00\:00")
if 950 is 9 hours, 50 minutes and 0 seconds.

Give the resulting formula a number format like:
hh:mm:ss
or whatever you want.

 

[Peo Sjoblom]

Try

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

where A1 holds the values like 950 1000 etc, note that it is important that
you format the cell with the formula as time

 

[Bryony]

Thank you BJ, that's brilliant!

try
=time(int(val/100),mod(val,100),0)

 

[Bryony]

Thanks Toppers, that's great.

try:

=TIME(LEFT(D1,LEN(D1)-2),RIGHT(D1,2),0)

 

[Bryony]

Thank you, this works too.

Is 950 9:50 AM or 9:50 PM?

Assuming these "times" are based on a 24hr clock and are either 3 or 4
characters in length:

950 = 9:50 AM
2150 = 9:50 PM

=TIME(LEFT(A1,IF(LEN(A1)=3,1,2)),RIGHT(A1,2),0)

I'm also assuming that you might have entries like 0030 (12:30 AM) and these
entries are formatted as TEXT (otherwise, as a number, Excel doesn't see the
leading 0s).

Biff

 

[Bryony]

Thanks Dave, that's brilliant.

You could use a formula like:
=--TEXT(A1,"00\:00\:00")
if 950 represents 0 hours, 9 minutes, 50 seconds

or
=--TEXT(A1,"00\:00")
if 950 is 9 hours, 50 minutes and 0 seconds.

Give the resulting formula a number format like:
hh:mm:ss
or whatever you want.

 

[jshehan]

I am trying to insert these formulas as suggested, however it keep giving me
errors???