Help with inputting time values and proper formatting

[BCarruth]

I have to input alot of time data into a spreadsheet and I am wondering if there is a way to just be able to put in the numbers and have it format to time for example:
I put in 1330 and it converts automatically to 1:30 PM?

 

[Blorf]

Hi.

Format B1 as time then put 1330 in a1, then put this in b1

=0+(LEFT(A2,2)&":"&RIGHT(A2,2))

and it will covert a1 to time for you in b1.

ChaZ

 

[byundt]

BCarruth,
Here is a Worksheet_Change sub that watches as you input data. If the data is numeric, it changes it to time--if you enter 1330, it becomes 1:30 P.M. You'll need to change the fourth line to reflect the range where you are inputting data. This sub needs to go in the worksheet code pane.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, targ As Range, InputCells As Range
Dim hr As Integer, min As Integer
Set InputCells = Range("A:A")       'Change to the cells that you'll be inputting time into
Set targ = Intersect(Target, InputCells)
If targ Is Nothing Then Exit Sub
Application.EnableEvents = False
On Error Resume Next
For Each cel In targ
    If IsNumeric(cel) Then
        hr = Left(cel, Len(cel) - 2)
        min = Right(cel, 2)
        cel = TimeSerial(hr, min, 0)
        cel.NumberFormat = "[$-409]h:mm AM/PM;@"
    End If
Next cel
On Error GoTo 0
Application.EnableEvents = True
End Sub

To paste the sub in the code pane for a worksheet, right-click the worksheet's sheet tab. Then choose View Code from the pop-up. Paste the code there, then ALT + F11 to return to the spreadsheet. If Excel won't let you put the code there, then open the Tools...Macro...Security menu item and change the security level to Medium.
Brad

 

[BCarruth]

Thanks for the help, I will try them both tomorrow!

Bob