Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Location
    West Hollywood, California, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Formatting in Cells. Crazy problem. (Excell 2003)

    Problem: we set the format of cells to something like Date, March 14, 2001. Then we type in the cell today's date: "031705," but the cell then fills with "December 24, 1985!" What's going on here? How can we simply type a date in a very simple way, such as the numbers, and have the date formatted as we choose?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Date Formatting in Cells. Crazy problem. (Excell 2003)

    Excel (or rather Windows) doesn't recognize 031705 as a date. You are in fact entering the number 31705 in the cell, and since it is formatted as a date, you see the date 31705 days after December 31, 1899, the origin of the date system Excel uses.
    The easiest workaround is to enter dates in a format that Excel recognizes, such as 3/17/05 or even 3/17 (Excel will assume the current year if you omit it). the alternative would be to write code in the On Change event of the worksheet, but you'd have to a lot of checking.

    By the way, you can enter the current date by typing <code>Ctrl+;</code> (semicolon)

  3. #3
    New Lounger
    Join Date
    Jan 2004
    Location
    West Hollywood, California, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Formatting in Cells. Crazy problem. (Excell 2003)

    Do you have any idea what code, and where to enter it. We wish to avoid typing the "/" when entering the date.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Formatting in Cells. Crazy problem. (Excell 2003)

    The code goes in the Worksheet Change event routine in the special module behind the worksheet where the dates will be entered. What the code is depends on EXACTLY what you want to enter (all possibilities), and what cells you want to enter it in, and what errors you want to check for. By that, I mean are you always going to enter 031705, or might someone enter 31705? Will anyone ever enter 03172005? etc., etc., etc.
    Legare Coleman

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date Formatting in Cells. Crazy problem. (Excell 2003)

    For some code, Checkout Chip Pearson's Date And Time Entry

    A workaround to avoid coding would be to have them enter the "number" in a cell (eg A1) and then use a formula to convert it:
    =DATE(2000+VALUE(RIGHT(A1,2)),VALUE(LEFT(RIGHT("0" &A1,6),2)),VALUE(MID(RIGHT("0"&A1,6),3,2)))

    The formula may be copied down the rows. This cell would be formatted for a date and you would use this in any formula/reference that you wanted the date...

    Steve

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Date Formatting in Cells. Crazy problem. (Excell 2003)

    Legare and Steve have already given you some ideas. Here is a piece of code.
    - Right click the sheet tab of the worksheet.
    - Select View Code from the popup menu.
    - Paste the following code into the worksheet module.
    - Replace B:B with the range you want to enter dates in.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Dim strVal As String
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("B:B")).Cells
    strVal = oCell.Value
    If Len(strVal) = 5 Then
    strVal = "0" & strVal
    End If
    If Len(strVal) = 6 Then
    strVal = Left(strVal, 2) & "/" & Mid(strVal, 3, 2) & "/" & Right(strVal, 2)
    If IsDate(strVal) Then
    oCell = DateValue(strVal)
    End If
    End If
    Next oCell
    Application.EnableEvents = True
    End If
    Set oCell = Nothing
    End Sub

    This allows you to enter today as 031705 or as 31705, but not as 03172005.

  7. #7
    New Lounger
    Join Date
    Jan 2004
    Location
    West Hollywood, California, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Formatting in Cells. Crazy problem. (Excell 2003)

    Sorry Hans, but I'm new to coding. Regarding your "B:B", what range are you talking about? Can you give an example. Also, do I type the quotation marks?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Date Formatting in Cells. Crazy problem. (Excell 2003)

    B:B is the way Excel refers to the entire column B.

    B25 means the rectangular range with upper left corner B2 and lower right corner D5.
    B means columns B to D in their entirety.
    2:5 means rows 2 to 5 in their entirety.

    In a formula, you use them just like that: =SUM(B25) or =MAX(B). In Visual Basic code, you use Range("B25") or Range("B"), including the quotes. So if you want the user to be able to enter a date as 031705 in the range C2:C100, replace both occurrences of Range("B:B") in the code I posted with Range("C2:C100").

  9. #9
    New Lounger
    Join Date
    Jan 2004
    Location
    West Hollywood, California, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Formatting in Cells. Crazy problem. (Excell 2003)

    Thanks a ton!

  10. #10
    New Lounger
    Join Date
    Mar 2005
    Location
    Wellington, Wellington
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Formatting in Cells. Crazy problem. (Excell 2003)

    There is another alternative. I've been experimenting with this for a while and just came across another option.

    You can use CustomProperties to set pseudo-meta-data.

    In the Workbook object, create a metadata item with:

    Private Sub Workbook_Open()
    ActiveSheet.CustomProperties.Add Name:="OldTarget", Value:=ActiveCell.Address
    End Sub

    and in the Worksheet:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    myOldTarget = ActiveSheet.CustomProperties.Item(1).Value
    [Do your fancy stuff about converting the date]
    ActiveSheet.CustomProperties.Item(1).Value = Target.Address
    End Sub

    It just avoids having to do the whole column each time. Creating the customproperty in this way retains the previous location value. You can of course check to make sure that the previous cell address is in the column you want, or any other validation condition.

    Also, if you are entering the "dates" all in the same format, you can format the column as "Text" and then, whenever you are ready, perform a "TextToColumns" function in excel and in step 3 set the column data format to the relevant date format.

    All of the options do carry risks as it relies upon consistency of data entry. Depending on what the fields are, and how much data entry is being done, I would advise to just use the date-slashes and set default dates wherever possible.

    With regard to the leading zero problem that Hans refers to, you could also use Data validation to enforce a 6 character entry.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Date Formatting in Cells. Crazy problem. (Excell 2003)

    I don't understand what setting the OldTarget custom property has to do with date conversion, but there are some points to be aware of:
    <UL><LI>The Target argument of the Worksheet_SelectionChange event procedure is not necessarily a single cell. If you select a range of cells, that will be the Target.
    <LI>The Worksheet_SelectionChange event occurs each time the user selects a different cell or range of cells, whether the previous target has been modified or not. Pressing the down arrow key ten times will cause the Worksheet_SelectionChange event to fire ten times.[/list]The Worksheet_Change event only occurs if a cell value has changed, it would seem to be more suitable if you want to convert values entered by the user.

  12. #12
    New Lounger
    Join Date
    Jan 2004
    Location
    West Hollywood, California, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Formatting in Cells. Crazy problem. (Excell 2003)

    Thanks! I'll try it next time I'm in the office with the doc.

Posting Permissions

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