hi all,
is this possible: i will type 011005 and then excel will automatically
format it as 01/10/05 and will be treated as date? thanks.
hi all,
is this possible: i will type 011005 and then excel will automatically
format it as 01/10/05 and will be treated as date? thanks.
Rufino
Only with VBA code or a function in another cell, there is no way with the
user interface of doing this automatically
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]HIS
"rufino palacol jr" <[email protected]> wrote in message
news:%23Ve5wjg%[email protected]...
> hi all,
>
> is this possible: i will type 011005 and then excel will automatically
> format it as 01/10/05 and will be treated as date? thanks.
hi Nick!
do you have a sample code there?
>
>Rufino
>
>Only with VBA code or a function in another cell, there is no way with the
>user interface of doing this automatically
>
>--
>HTH
>Nick Hodge
>Microsoft MVP - Excel
>Southampton, England
>[email protected]
>
>
>"rufino palacol jr" <[email protected]> wrote in message
>news:%23Ve5wjg%[email protected]...
>> hi all,
>>
>> is this possible: i will type 011005 and then excel will automatically
>> format it as 01/10/05 and will be treated as date? thanks.
>
>
Rufino
This in the worksheet_change event will work for entry in column A. You may
want to do some more work to account for different entry, e.g. I have
accounted for the fact that Excel strips the '0' off 01012004 making it
1012004, but it doesn't take account if for example someone enters a two
digit year
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ReEnable
If Not Application.Intersect(Target, Columns("A:A")) Is Nothing Then
Application.EnableEvents = False
If Len(Target.Value) = 8 Then
Target.Value = _
Left(Target.Value, 2) & "/" & _
Mid(Target.Value, 3, 2) & "/" & _
Right(Target.Value, 4)
End If
If Len(Target.Value) = 7 Then
Target.Value = _
Left(Target.Value, 1) & "/" & _
Mid(Target.Value, 2, 2) & "/" & _
Right(Target.Value, 4)
End If
Application.EnableEvents = True
Exit Sub
End If
ReEnable:
Application.EnableEvents = True
End Sub
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]HIS
"rufino palacol jr" <[email protected]> wrote in message
news:Osgus1g%[email protected]...
> hi Nick!
>
> do you have a sample code there?
>>
>>Rufino
>>
>>Only with VBA code or a function in another cell, there is no way with the
>>user interface of doing this automatically
>>
>>--
>>HTH
>>Nick Hodge
>>Microsoft MVP - Excel
>>Southampton, England
>>[email protected]
>>
>>
>>"rufino palacol jr" <[email protected]> wrote in message
>>news:%23Ve5wjg%[email protected]...
>>> hi all,
>>>
>>> is this possible: i will type 011005 and then excel will automatically
>>> format it as 01/10/05 and will be treated as date? thanks.
>>
>>
Try this Add-in for quick date entry.
http://www.rondebruin.nl/qde.htm
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Nick Hodge" <[email protected]> wrote in message news:ulHYeGh%[email protected]...
> Rufino
>
> This in the worksheet_change event will work for entry in column A. You may want to do some more work to account for different
> entry, e.g. I have accounted for the fact that Excel strips the '0' off 01012004 making it 1012004, but it doesn't take account if
> for example someone enters a two digit year
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo ReEnable
> If Not Application.Intersect(Target, Columns("A:A")) Is Nothing Then
> Application.EnableEvents = False
> If Len(Target.Value) = 8 Then
> Target.Value = _
> Left(Target.Value, 2) & "/" & _
> Mid(Target.Value, 3, 2) & "/" & _
> Right(Target.Value, 4)
> End If
> If Len(Target.Value) = 7 Then
> Target.Value = _
> Left(Target.Value, 1) & "/" & _
> Mid(Target.Value, 2, 2) & "/" & _
> Right(Target.Value, 4)
> End If
> Application.EnableEvents = True
> Exit Sub
> End If
> ReEnable:
> Application.EnableEvents = True
> End Sub
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> [email protected]HIS
>
>
> "rufino palacol jr" <[email protected]> wrote in message news:Osgus1g%[email protected]...
>> hi Nick!
>>
>> do you have a sample code there?
>>>
>>>Rufino
>>>
>>>Only with VBA code or a function in another cell, there is no way with the
>>>user interface of doing this automatically
>>>
>>>--
>>>HTH
>>>Nick Hodge
>>>Microsoft MVP - Excel
>>>Southampton, England
>>>[email protected]
>>>
>>>
>>>"rufino palacol jr" <[email protected]> wrote in message
>>>news:%23Ve5wjg%[email protected]...
>>>> hi all,
>>>>
>>>> is this possible: i will type 011005 and then excel will automatically
>>>> format it as 01/10/05 and will be treated as date? thanks.
>>>
>>>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks