Hello,



I have helped a friend with setting up a spreadsheet on his laptop. He has a HP Pavilion Entertainment PC, Model # dv5 -1225ca. His operating system is Windows 7 Home Premium.

The first column on his spreadsheet has been formatted to accept a date in the format of mm/dd/yy.

When he enters a date, in most cells, if the format is correct the date moves to the right. In some cells, the date will move to the left even though the format is correct.

I have checked the format for those cells and it is the correct format. I have tried removing/resetting the formatting, saved the worksheet, closed down Excel. Then restarted Excel, changed the format and the cells still act in the same way.

Any suggestions what might be causing this issue?

Thanks

DD

Hi,
It's because when the date is entered, Excel does not recognise it as a date so it interprets it as text. This is because of the regional settings. You don't specify the user's local; I live in the UK so I will explain this from a British perspective.

My regional settings in the control panel are English (United Kingdom). In the UK we write dates with the days before the months: dd/mm/yy. This is how Excel will expect my dates to be input into my spreadsheet.

If I open a new spreadsheet and input 01/02/10 into cell A1, Excel recognises it as a date (1st Feb 2010) and automatically adjusts it to 01/02/2010. This is how it appears both in the cell and in the formula bar (see picture 1 attached).

If I now format the cell with a format of mm/dd/yy, the cell displays the date as 02/01/10. However, the format only affects how the date is shown to the user. If you look in the formula bar, the date is still 01/02/2010. See picture 2 attached.

When you're putting dates into Excel, you must enter them as per your regional settings. If I now type in 05/04/2010 into cell A1, it will be interpreted as 5th April 2010 (even though the format is mm/dd/yy) so in the formula bar it will be saved as 05/04/2010. However, because the cell is formatted as mm/dd/yy, the date will be displayed in the cell as 04/05/10. See picture 3 attached.

If I type in 01/31/2010, there aren't 31 months in a year so Excel will not understand it as a date and will consider it to be a string. This is what is happening on your friend's PC. If you have an English (or similar locale) but have been typing in dates as mm/dd/yy then any entries which were interpretted as dates will have the months and days the wrong way around. They'll all need to be re-entered.

If you want to be able to type in dates using months first, then change the regional settings in the control panel.

Attached Images

	Picture1.JPG (8.2 KB, 9 views)
	Picture2.JPG (7.9 KB, 9 views)
	Picture3.JPG (7.6 KB, 9 views)

__________________
Colin

RAD Excel Blog

Colin,

Thanks for your reply. I will examine the regional settings on his computer and adjust them or the formatting to see if it will make a difference. I never would have looked at regional settings.
I will let you know what happens.

Thanks

DD

Colin,

Your instructions and solutions were perfect. Everything is now working as it should.

Many thanks.

DD