Entering dates
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Entering dates

  1. #1
    New Member
    Join Date
    Jan 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Prior to an upgrade from XL97 to XL2000 I was able to enter a date such as April 8 2002 as 080402. Now XL is interpreting 080402 as 80402 (dropping the leading 0) and the formatted result is February 17 2120. Any ideas on how I might be able to enter dates without requiring separater symbols such as '/' and '-'.

    Any feedback will be much appreciated.
    NM

  2. #2
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,262
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Entering dates

    If the field you want to enter the date this way in cell A1:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo notdate
    If Target.Address <> "$A$1" Then
    x = Str(Range("A1"))
    x = Format(x, "00\/00\/00")
    x = DateValue(x)
    Range("A1").Value2 = x
    Range("A1").NumberFormat = "m/d/yy"
    End If
    notdate:
    End Sub

  3. #3
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,832
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Entering dates

    format the cells where you want to enter dates as 00-00-00
    (menu format / cellproperties / tab "number" ==> choose custom and type 00-00-00)
    but the problem is that you can't count correctly with them entered in this way
    example 31-04-04 - 30-04-04 should be 1 but you'll get 10000

    regards,
    Erik
    I love Jesus

    email Erik

    founder of DRAFT

    my free Addins
    Table-It download & info
    Formula Translator 04

  4. #4
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,262
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Entering dates

      
    With the format cells option, if you need to refer to that value as a date, you can refer to it with a formula like this:

    =DATEVALUE(TEXT(A1,"00-00-00"))

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com