Converting numbers to 24 hour format

[excellenthelp]

Hello excellers,

I am wondering how we could convert numbers to 24 hour time format as we key in the numbers?

Example:
As we key in 0815, it would automatically convert to 08:00.
As we key in 1345, it would automatically convert to 13:45.

What is the code we could use to put in the code of the sheet?

Many thanks!

[Tony Valko]

See this...

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

[excellenthelp]

popipipo, thank you for the help. I am looking for what Tony Valko just shared. It has to be VBA code rather than a formula.

Thanks though!

[popipipo]

=TIME(LEFT(A1;2);RIGHT(A1;2);0)

Try this one

[excellenthelp]

WoW!

Perfect reference! This is exactly what I am looking for, Tony Valko.

Many thanks!!!

Marking this as SOLVED.

[Tony Valko]

You're welcome. Thanks for the feedback!

[Paul]

Hi Excellenthelp,

The following Worksheet_Change event should suffice:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("A:A")) Is Nothing Then _
    Target.Value = Format(TimeValue(Left(Target.Value, 2) & ":" & Right(Target.Value, 2)), "hh:mm")
    Application.EnableEvents = True
End Sub

Assumes you're entering values in column A.

Hope that helps!