Time Entries: Want to Skip the ":"

[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

[Gord Dibben]

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 <[email protected]> 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

[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


--
BizMark

[Brian Handly]

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