Time Entries: Want to Skip the ":"

B

Brian Handly

I have a time sheet with Start and Stop times. I want to enter 22:00 as
"2200". However, EXCEL does not recognize "2200" as a time. How can I
force it to recognize it as a time or how can I convert it to a time?

Texas Handly
 
B

BizMark

Here's a little routine I've just written which I think will solve your
problem.

Paste this into the 'ThisWorkbook' module of your workbook.
Replace GT with the Greater Than symbol and LT with the Less Than
symbol.

Note that the routine will only operate on cells formatted as 'Date',
so you can choose which cells get the treatment and which ones don't.

Code:===================================================

Public xLastRange As Excel.Range

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Excel.Range)
If Not IsEmpty(xLastRange) And TypeName(xLastRange) GT LT "Nothing"
Then
If xLastRange.Cells.Count = 1 Then
If InStr(1, xLastRange.NumberFormat, ":") GT 0 Then
If Format(xLastRange.Value, "hh:mm") LT GT
xLastRange.Text Then
tTimeAsDigits = Format(xLastRange.Value, "0000")
tTimeAsValue = Left(tTimeAsDigits, 2) & ":" &
Mid(tTimeAsDigits, 3, 2) & ":00"
If IsDate(tTimeAsValue) Then
dTimeAsValue = TimeValue(tTimeAsValue)
xLastRange.Value = dTimeAsValue
Else
xLastRange.Value = "#TIME?"
Beep
MsgBox ("Invalid time entered. Please
re-enter.")
End If
End If
End If
End If
End If

Set xLastRange = Target
End Sub




Hope this helps.
Regards,
BizMark
 
B

Brian Handly

BizMark said:
Here's a little routine I've just written which I think will solve your
problem.

Paste this into the 'ThisWorkbook' module of your workbook.
Replace GT with the Greater Than symbol and LT with the Less Than
symbol.

Note that the routine will only operate on cells formatted as 'Date',
so you can choose which cells get the treatment and which ones don't.

Code:===================================================

Public xLastRange As Excel.Range

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Excel.Range)
If Not IsEmpty(xLastRange) And TypeName(xLastRange) GT LT "Nothing"
Then
If xLastRange.Cells.Count = 1 Then
If InStr(1, xLastRange.NumberFormat, ":") GT 0 Then
If Format(xLastRange.Value, "hh:mm") LT GT
xLastRange.Text Then
tTimeAsDigits = Format(xLastRange.Value, "0000")
tTimeAsValue = Left(tTimeAsDigits, 2) & ":" &
Mid(tTimeAsDigits, 3, 2) & ":00"
If IsDate(tTimeAsValue) Then
dTimeAsValue = TimeValue(tTimeAsValue)
xLastRange.Value = dTimeAsValue
Else
xLastRange.Value = "#TIME?"
Beep
MsgBox ("Invalid time entered. Please
re-enter.")
End If
End If
End If
End If
End If

Set xLastRange = Target
End Sub




Hope this helps.
Regards,
BizMark
Click to expand...
Thanks
 
Top