Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing formating shortcut (Excel 2000)

    Applying CTRL+SHIFT+# gives a date format 01-Jan-05. Can one change that shortcut to format as mm/dd/yyyy or yy? I looked at the regional settings in the Control Panel and the short date was in the mm/dd/yy format already, so it looks as if these settings don't affect it. I like that shortcut in situations where I get a number format for the date I enter as Ctrl+;. That shortcut enables me to instantly change the format. Sometimes, it flashes by as mm/dd/yy then takes the final 01-Jan-05 format. TYIA/HNY

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: Changing formating shortcut (Excel 2000)

    Hans, the way I read the OP, I think he's also asking that your InsertDate macro include:

    Selection.Value = Date
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Changing formating shortcut (Excel 2000)

    Yep, I forgot to include that line - that's what you get for writing air code. Thanks for being alert. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

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

    Re: Changing formating shortcut (Excel 2000)

    Edited by HansV to add line - thanks to JohnBF for pointing out the omission.

    You can create a macro in your Personal.xls:

    Sub InsertDate()
    On Error Resume Next
    Selection.Value = Date
    Selection.NumberFormat = "mm/dd/yyyy"
    End Sub

    To assign this to Ctrl+Shift+#, you have to use a little trick - you can't do it the standard way.
    Double click the ThisWorkbook node of Personal.xls in the Visual Basic Editor.
    Enter this code:

    Private Sub Workbook_Open()
    Application.OnKey "+^3", "InsertDate"
    End Sub

    See <!post=Personal.xls Tutorial (All),118382>Personal.xls Tutorial (All)<!/post> if you need information about the personal macro workbook.

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing formating shortcut (Excel 2000)

    Thanks for the solution! I thought it would be a little tricky...will read the xls file tomorrow and maybe learn something. Thanks again.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing formating shortcut (Excel 2000)

    Not in the same league as all the above wise and heavy stuff, but don't forget the handy paintbrush thing for copying an existing format to other cells. Double click it and it stays active until you click it again.

Posting Permissions

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