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.

try
=time(int(val/100),mod(val,100),0)
"Bryony" wrote:

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.

try:

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

"Bryony" wrote:

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.

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

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.



Bryony wrote:

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.


--

Dave Peterson

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


--
Regards,

Peo Sjoblom


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

Thank you BJ, that's brilliant!

"bj" wrote:

try
=time(int(val/100),mod(val,100),0)
"Bryony" wrote:

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.

Thanks Toppers, that's great.

"Toppers" wrote:

try:

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

"Bryony" wrote:

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.

Thank you, this works too.

"T. Valko" wrote:

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

Thanks Dave, that's brilliant.

"Dave Peterson" wrote:

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.



Bryony wrote:

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.


--

Dave Peterson

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

"Toppers" wrote:

try:

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

"Bryony" wrote:

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.