Entering time using decimal place or Colon

A

Alethea

Im looking for a way to allow the users of my spreadsheet to enter the
time using a . or a :, so 7.30 would convert to 07:30 (and 7:30 would
be acceptable input as well).

I know this is possible but I just cannot seem to figure out how, any
help would be appreciated.
 
B

Bob Phillips

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If IsNumeric(.Value) Then
.Value = (Int(.Value) + (.Value - Int(.Value)) * 100 / 60) /
24
.NumberFormat = "hh:mm"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
R

RagDyeR

See if this old post helps:

http://tinyurl.com/r3ngw

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


message
Im looking for a way to allow the users of my spreadsheet to enter the
time using a . or a :, so 7.30 would convert to 07:30 (and 7:30 would
be acceptable input as well).

I know this is possible but I just cannot seem to figure out how, any
help would be appreciated.
 
A

Alethea

Thanks for the help Bob this is definately a step in the right
direction, the only problem is that while the code converts 7.30 to
07:30 it will not allow the user to enter 07:30 (it outputs 00:31).

Any suggestions? (and thanks again, I really appreciate it).

Alethea
 
B

Bob Phillips

Hi Alethea,

This should do it

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If IsNumeric(.Value) And .Value >= 1 Then
.Value = (Int(.Value) + (.Value - Int(.Value)) * 100 / 60) /
24
.NumberFormat = "hh:mm"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
A

Alethea

heh heh thats a neat trick RD and Im sure its not what they intended the
Autocorrect feature for!

If it was for myself it would be ideal (and I have actually used it for
one of my sheets), the problem is that other people are entering the
times and whenever there is a problem I can guarantee that they have
entered . rather than :.

Since I cannot bring a cattle prod to work in order to rectify this
behaviour the best solution would be a system that will allows both
forms of input, converting the . to a : when entered.

Thanks!
 
Top