+ Reply to Thread
Results 1 to 5 of 5

input date

  1. #1
    rufino palacol jr
    Guest

    input date

    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.

  2. #2
    Nick Hodge
    Guest

    Re: input date

    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.




  3. #3
    rufino palacol jr
    Guest

    Re: input date

    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.

    >
    >


  4. #4
    Nick Hodge
    Guest

    Re: input date

    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.

    >>
    >>




  5. #5
    Ron de Bruin
    Guest

    Re: input date

    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.
    >>>
    >>>

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1