Windows 7: 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

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

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

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

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.

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.

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



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



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



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



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




OS

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

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

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).