Time format

[kras]

Hi,

In order a cell to dispaly the following time format hh:mm:ss I need to format it to Time or Custom format. Then, to enter a time I need to type hour, minutes and seconds (for example 13:58:06), using : as a separator between them. My question is: Is it possible to format a cell in such a way so that I could enter time without using : as a separator for hours, minutes and seconds? For example, I would like to enter 135806 and have 13:58:06 displayed on a cell.

Thank you,

kras

[sweep]

Hi,

You can convert the string of six numbers into a colon separated time like this:

=LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2)

[kras]

Hi Dave,

Thank you for your reply.

It works when you refer to other cell in the workbook. The problem is that I would like to have it displayed in the same cell I enter the time (for example, I type 135608 in A1 and I would like to have 13:56:08 in A1). Perhaps, it is a custom formatting issue, but I couldn't figure it out.


kras

[Richard Schollar]

Hi Kras

You'd need some VBA code attached to a worksheet event to achieve this - you can't do it just thru number formatting I'm afraid.

Richard

[Dean England]

Here's something I put together for someone a week or so ago. See if the attachment is what you want. The range can be changed to suit your needs.

Dean

[kras]

Thank you both Richard and Dean.

[Dean England]

Glad to have helped. Thanks for the feedback.

Dean