Format Cells in Excel

[SamDelhi]

Hi Just wondering if there is a way to format Excel cells in a particular way

like for eg, if cell contains time as a value, typing like 15:30 is kind of cumbersome because i have to alternate between the numeric keypad and the alpha keypad. is there a way to format a column or row in excel in which i type "0330 " it automatically become 15:30 or it becomes 3:30 pm on its own. that way i can save a lot of time when there are thousands of entries to be made

[#2]

@SamDelhi, the best you can do is to enter the numbers in a column, then convert those numbers to times in the next column, as shown in the snapshot below: 

[#3]

@SamDelhi 

You could also write a VBA event handler

Private Sub Worksheet_Change(ByVal Target As Range)
Dim hrs As Double, min As Double, time As Double
    If Not Intersect(Target, Range("date")) Is Nothing Then
    Application.EnableEvents = False
        hrs = CLng(Left(Target.Value, 2)) / 24
        min = CLng(Right(Target.Value, 2)) / 24 / 60
        time = hrs + min
        Target.Value = time
        Target.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"
    Application.EnableEvents = True
    End If
End Sub

The above should handle 4 digits placed within a text-formatted range "date" but the task grows if you wish to trap 3690 as an error, or handle 830 as if it were 0830, or 5 as 0500.