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

[CiaraJansonFan]

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

 

[Orbital Shark]

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

 

[Orbital Shark]

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

 

[CiaraJansonFan]

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

 

[Petey7]

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.

 

[Fayla]

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.

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

 

[Orbital Shark]

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.

   Note

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

OS

 

[CiaraJansonFan]

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

 

[CiaraJansonFan]

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

 

[Petey7]

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

 

[Orbital Shark]

Can you post a snip of the following please?

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

Under the formats tab click 'Additional Settings...' (post that snip)

 

[CiaraJansonFan]

Here you go:

 

[Orbital Shark]

I notice that you have "ddd" in front of the date format ("ddd dd/mm/yyy"). Try removing that as it could be what's conflicting.

 

[Orbital Shark]

Ok, i've isolated that it is indeed the "ddd" that's causing the issue as i've just tried it and all date formats in Excel (even 2010) now have spaces seperating them...

With "ddd"


Without "ddd"

 

[CiaraJansonFan]

Yeah, you're right - but that's ridiculous! Why can't I show the day AND have slashes separating the day, month and year? That's the way I had XP set up

I want to see the DAY in my notification area, and in Task Scheduler, not have to look at a calendar to find out what day 02/11/2010 was!

How do I go about reporting this bug to Microsoft?!!?

Cheers
Steven

 

[Orbital Shark]

It's a very strange fault (for want of a better word) so I've highlighted my findings in a new thread in the hope that someone may have found a way to work around it.

http://www.sevenforums.com/general-discussion/123214-custom-date-settings-inconcistencies.html

 

[kronckew]

i've found the system format uses whatever the char. is after the initial ddd as the seperator, if you use

ddd dd/mm/yyyy you get Mon 08 11 2010
ddd, dd/mm/yyyy you get Mon, 08,11,2010

dddd dd/mm/yyyy similarly gives Monday 08 11 2010
dddd, dd/mm/yyyy gives Monday, 08,11,2010

unfortunately ddd/ .... gives Mon/ 08/11/2010.

in excel 2007, using ddd dd/mm/yyyy system setting, i could force cell formatting to the desired one by using the custom format of ddd dd/mm/yyyy, even if the date format was invalid.

my task scheduler dates show as Mon 08/11/201 as the string doesn't fit the space by one char. using the short year would fix that, ie. Mon 08/11/10.

to avoid ambiguity i always set my date string to military format dd mmm yyyy , giving 08 Nov 2010 which is unambiguous between the UK and my friends & relatives in the US.

 

[CiaraJansonFan]

It just has to be a bug in Windows 7 doesn't it?!!?

I mean if I select a custom date format of ddd dd/MM/yyyy, I do NOT want the slashes to be replaced with spaces, if I did I would have chosen ddd dd MM yyyy wouldn't I?!!? In fact, changing between those 2 date formats has absolutely NO effect on the dates shown in Task Scheduler, which it should do of course

Get it fixed Microsoft!!!

Steven

 

[CiaraJansonFan]

I was convinced that I had XP set up to show the day as well as the date, but it turns out I was mistaken and that you can reproduce this bug in Windows XP too - that makes it even worse!!

 

[bigmantonyd]

I ran into the same problem and I'm very thankful to have found this thread. As a workaround, I changed my short date format to MM/dd ddd. It's not ideal, but it fixes the problem and displays the same information (albeit in an unorthodox order). Thanks!