Date format using space as date separator rather than forward slash!

Page 1 of 2 12 LastLast

  1. Posts : 31
    Windows 7 Home Premium 32-bit
       #1

    Date format using space as date separator rather than forward slash!


    Hi all,

    I'm new to Windows 7 (Home Premium 32-bit) and I just can't figure out what's going on here! Basically I want my short date format to be dd/mm/yyyy (UK format) e.g. 05/11/2010, but when I key a date into Excel 2002 it is appearing as 05 11 2010. In Control Panel I have the short date format set to ddd dd/MM/yyyy and the example is shown as Fri 05/11/2010, but in Excel all the date formats are shown with space separators, e.g. 14 03 2001! I don't think it's an Excel problem though as in Windows Task Scheduler the dates are shown with spaces there e.g. '19:25 on Mon 01 11 2010'. The date by the clock though is shown correctly with slash separators!

    I know in Windows XP you can select the date separator from a drop-down list, but this option seems to be absent in Windows 7. Under HKEY_CURRENT_USER\Control Panel\International in the registry, sDate was set to a space, but changing that to a slash (and rebooting) didn't fix the problem.

    Does anyone have any ideas please what's wrong and how to fix it?

    Thanks
    Steven
      My Computer


  2. Posts : 6,305
    Windows 7 Ultimate x64
       #2

    Hi,

    The option you're after is controlled from within Excel. If you right-click the cell(s) in question you can select 'format cells' and under 'Date' you can select the desired format.


    OS
      My Computer


  3. Posts : 6,305
    Windows 7 Ultimate x64
       #3

    Can you not make a custom date format? e.g. using "dd/mm/yyyy"
      My Computer


  4. Posts : 31
    Windows 7 Home Premium 32-bit
    Thread Starter
       #4

    Orbital Shark said:
    Hi,

    The option you're after is controlled from within Excel. If you right-click the cell(s) in question you can select 'format cells' and under 'Date' you can select the desired format.


    OS
    Thanks, but ALL the date formats in Excel use space separators instead of slashes! Also like I said the Windows Task Scheduler is using space separators too, so it's ignoring the registry setting
      My Computer


  5. Posts : 2,963
    Windows 7 Professional SP1 64-bit
       #5

    It doesn't matter what you do in the registry. The date format is controled from within Excel, which is not a part of Windows 7. I was able to set the date in Excel 2010 by right-clicking the cell, as Obital instructed, and selecting format cell. I set the location to English (U.K.) and selected the first option.
      My Computer


  6. Posts : 826
    Windows 7 Professional 64 Bit SP1
       #6

    Apparently Excel 2002 has a custom date format: Custom Date Format Excel 2002 | TheDailyReviewer

    I haven't used Excel 2002 in a long time, but I know that in Excel 2007 you just select the fields you want, choose to format the cells, then you can click 'custom' and input your date format required.

    Also, your other programs may be reading your long date format, not the short one.

    CiaraJansonFan said:
    Orbital Shark said:
    Hi,

    The option you're after is controlled from within Excel. If you right-click the cell(s) in question you can select 'format cells' and under 'Date' you can select the desired format.


    OS
    Thanks, but ALL the date formats in Excel use space separators instead of slashes! Also like I said the Windows Task Scheduler is using space separators too, so it's ignoring the registry setting
      My Computer


  7. Posts : 6,305
    Windows 7 Ultimate x64
       #7

    See if the following steps will help,

    Open Control Panel -> Clock, Language, and Region -> Region and Language

    1, Under 'Formats' make sure that English - (United Kingdon) is selected

    Date format using space as date separator rather than forward slash!-1-region-formats.png

    2, Under 'Location' make sure that United Kingdom is selected

    Date format using space as date separator rather than forward slash!-2-region-location.png

    3, Under 'Keyboard and Languages' and 'Change Keyboards' make sure that the 'Default Input Device' is set to English (United Kingdom) - United Kingdom

    Date format using space as date separator rather than forward slash!-3-region-keyboards.png

    4, Under 'Administrative' click 'Copy Settings'. Make sure that both settings at the bottom are selected and click 'OK'

    Date format using space as date separator rather than forward slash!-4-region-copy-settings.png

    Now try in Excel to see if it has had any effect.

       Note
    This has helped in gaining additional settings from within certain application within Windows



    OS
      My Computer


  8. Posts : 31
    Windows 7 Home Premium 32-bit
    Thread Starter
       #8

    Petey7 said:
    It doesn't matter what you do in the registry. The date format is controled from within Excel, which is not a part of Windows 7. I was able to set the date in Excel 2010 by right-clicking the cell, as Obital instructed, and selecting format cell. I set the location to English (U.K.) and selected the first option.
    What's the format for the first example then please? Mine are as follows:

    *14 03 2001
    *Wed 14 Mar 2001
    14 03 2001
    14 03 01
    14 3 01
    14.3.01
    2001-03-14

    As you can see, none of these have slash separators!

    If I boot up into XP (same version of Excel), the separators are shown as slashes:

    *14/03/2001
    14/03/2001

    etc.

    Thanks
    Steven
      My Computer


  9. Posts : 31
    Windows 7 Home Premium 32-bit
    Thread Starter
       #9

    Orbital,

    No effect whatsoever I'm afraid! Both Task Scheduler and Excel are still showing space separators. I've also checked my netbook running Windows 7 Starter and the Task Scheduler on that is the same. Can you check your Task Scheduler please and let me know how the dates are formatted?

    Mine is attached.

    Thanks
    Steven
    Attached Thumbnails Attached Thumbnails Date format using space as date separator rather than forward slash!-task-scheduler-date-format.jpg  
      My Computer


  10. Posts : 2,963
    Windows 7 Professional SP1 64-bit
       #10

    Mine show as:
    14/03/2001
    14/03/01
    14/3/01
    14. 3. 01.
    2001-03-14

    It seems unusual that you would randomly show spaces instead of slashes. For the time being I recommend uninstalling Office completely then reinstalling it, incase something went wrong during the install process. Also, what are you typing when you enter the date (i.e. =now(), =date(year,month,day), or just typing it in as 14/03/2001).
      My Computer


 
Page 1 of 2 12 LastLast

  Related Discussions
Our Sites
Site Links
About Us
Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

© Designer Media Ltd
All times are GMT -5. The time now is 04:19.
Find Us