I am working with data in Excel and have time stamps in the format of 24hr
time but it does not have a colon. For example 1321 or 843 instead of what I
want 13:21 or 8:43.

Is there a way to either add a colon or change the format into a time
format. (I already tried just highlighting to change the format to time; it
doesn't work.)

You would need VBA or help formulas

http://www.cpearson.com/excel/DateTimeEntry.htm


--


Regards,


Peo Sjoblom




"Erin" wrote in message
...
I am working with data in Excel and have time stamps in the format of 24hr
time but it does not have a colon. For example 1321 or 843 instead of what
I
want 13:21 or 8:43.

Is there a way to either add a colon or change the format into a time
format. (I already tried just highlighting to change the format to time;
it
doesn't work.)

Hi,

Assuming you have typed in 1321 in cell A1, enter the following formula in
cell B1

LEFT(A1,IF(LEN(A1)=4,2,1))&":"&RIGHT(A1,2). Now format the cell to Time.

Regards,

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Peo Sjoblom" wrote in message
...
You would need VBA or help formulas

http://www.cpearson.com/excel/DateTimeEntry.htm


--


Regards,


Peo Sjoblom




"Erin" wrote in message
...
I am working with data in Excel and have time stamps in the format of 24hr
time but it does not have a colon. For example 1321 or 843 instead of
what I
want 13:21 or 8:43.

Is there a way to either add a colon or change the format into a time
format. (I already tried just highlighting to change the format to time;
it
doesn't work.)

First of all that formula will return text so it doesn't help if you format
as time

=--(LEFT(A1,IF(LEN(A1)=4,2,1))&":"&RIGHT(A1,2))

then format as time, however if 10 means 00:10 then your formula fails




but if are going to use a formula then it is easier to use

=TIME(INT(A1/100),MOD(A1,100),0)

and even easier to use

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


--


Regards,


Peo Sjoblom





"Ashish Mathur" wrote in message
...
Hi,

Assuming you have typed in 1321 in cell A1, enter the following formula in
cell B1

LEFT(A1,IF(LEN(A1)=4,2,1))&":"&RIGHT(A1,2). Now format the cell to Time.

Regards,

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Peo Sjoblom" wrote in message
...
You would need VBA or help formulas

http://www.cpearson.com/excel/DateTimeEntry.htm


--


Regards,


Peo Sjoblom




"Erin" wrote in message
...
I am working with data in Excel and have time stamps in the format of
24hr
time but it does not have a colon. For example 1321 or 843 instead of
what I
want 13:21 or 8:43.

Is there a way to either add a colon or change the format into a time
format. (I already tried just highlighting to change the format to time;
it
doesn't work.)

=--TEXT(A2,"00\:00")
and format as time.
--
David Biddulph

"Erin" wrote in message
...
I am working with data in Excel and have time stamps in the format of 24hr
time but it does not have a colon. For example 1321 or 843 instead of what
I
want 13:21 or 8:43.

Is there a way to either add a colon or change the format into a time
format. (I already tried just highlighting to change the format to time;
it
doesn't work.)