converting a 4 figure number to time in active cell

  • Thread starter Thread starter Pee
  • Start date Start date
P

Pee

Hi, I've got 2007 on my pc and I'm trying to set up a timesheet. As I'm to
lazy to keep entering the ":" all the time, i was wondering if someone knew
how to change 1530 to 15:30 etc in the active cell for all cells G5 to I19
on all 5 sheets in my workbook!!!

I think this has something to do with vba or macros and i've never used them
before.

If anyone can help me, I'd be very grateful.

Thanks
Pete
 
Thanks for this, I've got it sort of working but only for the g column.
Any suggestions on how to get it working for g5-g19 and h5-19 and I5-i19?

Thanks again,
Pete



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Range("G5:G19")) Is Nothing Then
Exit Sub

End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub
 
Doh, the answer was blindingly obvious!

If Application.Intersect(Target, Range("G5:I19")) Is Nothing Then

Cool - thanks again, I'm most grateful to you

:o)
 
Back
Top