Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Jacksonville, North Carolina, USA
    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
    Pittsburgh, Pennsylvania, USA
    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.

    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
    lYear = lYear + 1900
    End If
    rCell.Value = DateSerial(lYear, lMonth, lDay)
    Next rCell
    Application.EnableEvents = True
    End If
    End Sub</pre>


  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Melbourne, Victoria, Australia
    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:

    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