What API code do I use if I want to quickly enter 24 hour times (without entering the colon and still retaining time formatting) for a certain range of cells?

I know the following code works for Excel spreadsheets, but not for Google spreadsheets. How would I get this to work in Google?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vVal

    If Target.Cells.Count > 1 Then Exit Sub

    If Intersect(Target, Range("c1:zz5000")) Is Nothing Then Exit Sub

    With Target
        vVal = Format(.Value, "0000")
        If IsNumeric(vVal) And Len(vVal) = 4 Then
            Application.EnableEvents = False
            .Value = Left(vVal, 2) & ":" & Right(vVal, 2)
            .NumberFormat = "[h]:mm"
        End If
    End With

    Application.EnableEvents = True
End Sub
  • Could you provide a link to the spreadsheet you created? – Rob Jan 3 '14 at 20:00
  • I'd really like to be able to enter on the Rosters sheet 0700 instead of 07:00 or 1800 instead of 18:00 for example. Any help you can offer would be great. – Adam Watts Jan 4 '14 at 2:53
  • However, I still need to retain the time format. – Adam Watts Jan 4 '14 at 2:54
  • Can you not do this with a formula, i.e. an extra cell with a formula that converts your 1700 into a time value. – eddyparkinson Jan 5 '14 at 23:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.