Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date field mask (2003)

    Hi all,
    I am trying to create a mask or custom date format for a spreadsheet. What I am trying to do is have a user enter for example 030205 and once the user clicks out of the cell I want the date displayed as 3 Feb 05 or 03/02/05. Can this be done?
    I thought it would be possible to do this by creating a custom date field, but this doesn't seem to be the case.
    Any advice appreciated.

    Bill

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

    Re: Date field mask (2003)

    Code similar to that posted by Legare Coleman in <post#=209941>post 209941</post#> may do what you want, but I have always found this tricky - it's hard to make the code work correctly on systems with different date settings.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date field mask (2003)

    Thanks Hans. I will give it a go.

    Bill

  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 field mask (2003)

    Edited by Legare Coleman to fix a problem in the code.

    You can't really do that directly. You can get what you are asking with some caveats. The VBA code below, placed in the worksheet change event routine for the sheet in question will convert any number like you specified entered into column A into a date and store that date into the same cell. However, if the user enters anything else into the cells, the VBA code will either get an error or convert it to gibberish. You can format the cells to display the date in any format you want.

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


    You can change the range affected by changing the statement below to specify the range you want:

    <pre> Set oRngForDates = Range("A:A")
    </pre>

    Legare Coleman

Posting Permissions

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