Convert integer to time query
 

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.

Convert integer to time query
 

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.

Convert integer to time query
 

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.

Convert integer to time query
 

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.

Convert integer to time query
 

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

Convert integer to time query
 

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.

Convert integer to time query
 

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.

Convert integer to time query
 

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.

Convert integer to time query
 

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.

Convert integer to time query
 

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

Convert integer to time query
 

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.