Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Somewhere Over the Rainbow
    Posts
    232
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hello,

    I'm having trouble with the simple date formatting in a cell.
    For example: For today's date I want to type 051109 and when I tab away I would like it to become 05/11/2009.
    I have the cell formatting set to: mm/dd/yyyy
    When I enter 051109 I get 12/5/2039.

    Thanks for all your help again,
    HandyAndy
    Time can fix anything.....even a broken clock. - Handy Andy

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This would not be a "simple date formatting" since the information you are entering is not a date but a number. If you want the change to be instant you need to use a Worksheet_Change event macro. 12/5/2039 would be the 51109th day after 12/31/1899 - day one being 1/1/1900

    The following assumes the date field is in the B column and the date will all be in the current century.
    Right click on the tab, choose View Code and paste the following:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strDate As String
    
    If Target.Column <> 2 Or IsNumeric(Target) = False Then
    	Exit Sub
    Else
    	strDate = Right("0" & Target, 6)
    	Target = DateSerial(Right("20" & strDate, 2), Left(strDate, 2), Mid(strDate, 3, 2))
    End If
    
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Somewhere Over the Rainbow
    Posts
    232
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks, but it doesn't seem to be correct all the time.
    Nice Groucho Quote by the way

    [quote name='mbarron' post='774778' date='11-May-2009 22:13']This would not be a "simple date formatting" since the information you are entering is not a date but a number. If you want the change to be instant you need to use a Worksheet_Change event macro. 12/5/2039 would be the 51109th day after 12/31/1899 - day one being 1/1/1900

    The following assumes the date field is in the B column and the date will all be in the current century.
    Right click on the tab, choose View Code and paste the following:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strDate As String
    
    If Target.Column <> 2 Or IsNumeric(Target) = False Then
    	Exit Sub
    Else
    	strDate = Right("0" & Target, 6)
    	Target = DateSerial(Right("20" & strDate, 2), Left(strDate, 2), Mid(strDate, 3, 2))
    End If
    
    End Sub
    [/quote]
    Time can fix anything.....even a broken clock. - Handy Andy

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In what instances is it not correct?

  5. #5
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Somewhere Over the Rainbow
    Posts
    232
    Thanks
    0
    Thanked 1 Time in 1 Post
    Sorry, it is correct, but try to type another date into the cell after you entered one before.
    Also, how does it know if a year is 1900 or 2000 just by the last to digits, because some dates come up as 19--

    Thanks again.
    Time can fix anything.....even a broken clock. - Handy Andy

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [quote name='HandyAndy' post='774784' date='12-May-2009 12:46']Sorry, it is correct, but try to type another date into the cell after you entered one before.
    Also, how does it know if a year is 1900 or 2000 just by the last to digits, because some dates come up as 19--

    Thanks again.[/quote]
    Hi HandyAndy,

    Try:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strDate As String
    Application.EnableEvents = False
    If Target.Column <> 2 Then Exit Sub
    If IsNumeric(Target) Then
      strDate = Format(Target, "000000")
      Target = DateSerial(2000 + Right(strDate, 2), Left(strDate, 2), Mid(strDate, 3, 2))
    ElseIf IsDate(Target) Then
      strDate = Format(DateSerial(Year(Target), Month(Target), Day(Target)), "000000")
      Target = DateSerial(2000 + Right(strDate, 2), Left(strDate, 2), Mid(strDate, 3, 2))
    End If
    Application.EnableEvents = True
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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