time formating

[hmonat]

Hi, I have to enter lots of data in time format. is there a way to format a cell so that it automatically enter the semicolumn ) after 2 digits?? for example if I type 2345, it would format it as 23:45.

 

[Puertorekinsam]

let's assume your times are in column A, starting with cell A1
once you are done, insert a column to the right of your time
type the following in cell B1 =left(A1,2)&":" & right (A1,2)

Double click the dark black square on the bottom right of cell B1 to fill for the whole column of dates
copy column B
Paste special Values in column A
Delete Column B

 

[hmonat]

Thanks for the hint, however when I need to enter time 0023, the formula result is 23:00 where it shud be 00:23, does it have something to do with how the cell is formatted?? (Time, number, general, etc...)

 

[SpillerBD]

Set all cells that you will enter data into as TEXT formatted cells. Then make your entries.

 

[joeu2004]

Hi, I have to enter lots of data in time format. is there a way to format a cell so that it automatically enter the semicolumn [it is a colon] after 2 digits?? for example if I type 2345, it would format it as 23:45.

when I need to enter time 0023, the formula result is 23:00 where it shud be 00:23

If your data is in A1:A1000, enter the following into B1 and copy down through B1000:

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

If you wish, you can copy B1:B1000 and paste-value into A1:A1000 to replace the original data; then delete B1:B1000.

I assume you want a numeric time result, as if you had typed 23:45. The double-negate ("--") converts the text result to numeric time.

If you want text, as Puertorekinsam's formula left you, simply omit the double-negate.