Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Jacksonville, North Carolina, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel - Date Format (2000 (9.0.2720))

    How do I format a date so that when I enter 010102 I get 01/01/02 instead of 08/29/27?

  2. #2
    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: Excel - Date Format (2000 (9.0.2720))

    enter 01/01/02 formatted as "mm/dd/yy" will do it.

    Excel will autoconvert and calc a serialdate. by entering in the NUMBER 10,102 excel does NO conversion since it is already a number and gives you the date that is 10,102 days past Jan 1, 1900.

    If you want to convert, you will have to use a function or to automatically do it, create a macro.

    if the date is in A1 this function will calculate the serial date in another cell.
    =DATE(A1-INT(A1/10000)*10000-INT((A1-INT(A1/10000)*10000)/100)*100+2000,INT(A1/10000),INT((A1-INT(A1/10000)*10000)/100))

    This assumes ALL years are in 2000.

    If you want an automacro, the following will convert anything in Col A. This assumes <30 are 2000 and >= 30 are 1900 (like XL97)
    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Dim rCell As Range
    Dim lYear As Long
    Dim lMonth As Long
    Dim lDay As Long
    Application.EnableEvents = False
    For Each rCell In Intersect(Target, Range("A:A"))
    lMonth = Int(rCell.Value / 10000)
    lDay = Int(rCell.Value - lMonth * 10000) / 100
    lYear = rCell.Value - lMonth * 10000 - lDay * 100
    If lYear < 30 Then
    lYear = lYear + 2000
    Else
    lYear = lYear + 1900
    End If
    rCell.Value = DateSerial(lYear, lMonth, lDay)
    Next rCell
    Application.EnableEvents = True
    End If
    End Sub</pre>


    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Excel - Date Format (2000 (9.0.2720))

    (Edited by HansV to activate URL - see <!help=19>Help 19<!/help>)

    Hi steve and bbrown5,

    Chip Pearson has a similar macro to the one Steve has supplied at: http://www.cpearson.com/excel/DateTimeEntry.htm

    I have used a similar macro in a workbook for several years and found it most effective, but I did expand it to permit the normal format data entry as well as the quick entry.

    Good Luck

    Peter Moran
    Two heads are always better than one!

Posting Permissions

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