Results 1 to 4 of 4
  • Thread Tools
  1. 5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date custom format (XP)

    Want to format cells to display a 4/11/02 result WITHOUT the user having to type the forward slashes. Can this be done and if it can, how the heck to you do it???

    Thanks

    Linda

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

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

    Re: Date custom format (XP)

    If the dates are in column A, the code below placed in the worksheet change event routine should do that:

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Dim strVal As String
    If Not Intersect(Target, ActiveSheet.Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, ActiveSheet.Range("A:A"))
    strVal = Format(oCell.Value, "000000")
    oCell.Value = DateSerial(Right(strVal, 2), Mid(strVal, 3, 2), Left(strVal, 2))
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub
    </pre>

    Legare Coleman

  4. 5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date custom format (XP)

    I'm the idiot you've helped B4 - apparently still having brain trouble - no clue where I'm supposed to write this routine.

    Sorry -

  5. Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date custom format (XP)

    Linda,

    Legare's code should go into the relevant worksheet codepane. To get to that just right click on th erelevant sheet tab, and select View Code. You can then post the code, but you will need to change the reference A:A (which is all column A) to the particular range you want this event to happen. The code is rigid in that any numeric value will be converted to a date, and nonnumeric entries or deleting existing entries will lead to errors.

    As a partial remedy for that perhaps the following amendment to Legare's code might assist (hope he does not mind) :<pre>Dim oCell As Range
    Dim strVal As String
    If Not Intersect(Target, ActiveSheet.Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, ActiveSheet.Range("A:A"))
    If IsNumeric(oCell.Value) Then
    strVal = Format(oCell.Value, "000000")
    oCell.Value = DateSerial(Right(strVal, 2), _
    Mid(strVal, 3, 2), Left(strVal, 2))
    End If
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub</pre>

    Andrew C

Posting Permissions

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