Time Entries: Want to Skip the ":"
 

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

Time Entries: Want to Skip the ":"
 

Brian

See Chip Peason's site for date/time "quick entry" methods.

http://www.cpearson.com/excel/DateTimeEntry.htm


Gord Dibben Excel MVP

On Sun, 18 Dec 2005 12:00:22 -0600, Brian Handly wrote:

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

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

Time Entries: Want to Skip the ":"
 

BizMark wrote:
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


Thanks