date formatting issue

**MP3265** · 02-04-2013, 04:05 PM

I am having issues with regards to the format of dates on a spreadsheet. The date is showing up on my sheet as 2010-09-27 despite the formatting being set to mm-dd-yy.

Actually, now that I look it doesn't matter what I change the format to, the date continually is displaying yyyy-mm-dd as shown. Even if I change the format to general or number, it is displaying 2010-09-27 and not the number associated with that date. Has anyone ever seen this where you can't change the format?

Thanks in advance for the help!

**daddylonglegs** · 02-04-2013, 04:22 PM

If you can't change the format then it's text-formatted

If you have a column of such dates try converting using "text to columns" functionality. Select column then use

Data > text to columns > Next > Next > under "column data format" select "date" and the source format from the dropdown (YMD) > Finish

**Special-K** · 02-04-2013, 04:22 PM

Maybe it's not a date, maybe it's text.
Also check your Windows Regional and Language Options in the control panel are correct.

**RHCPgergo** · 02-04-2013, 04:24 PM

The data in the cell is probably stored as text, not date. You can easily see that: if it is aligned left by default, it is text, but if it is aligned right by default, it's a number (or date).

If it's as I suspected, you should use text-to-columns to have your date recognized as a date. After that you can format it any way you want. The link below describes a way. http://www.myonlinetraininghub.com/e...t-date-formats

**MP3265** · 02-06-2013, 02:10 PM

Hey all, appreciate the help. The date is stored as a date. Really is very strange but I am unable to change it to the correct format. Do you have any other guesses as to what might be causing the issue?

Thanks.

**Kevin UK** · 02-06-2013, 02:56 PM

Hi MP3265

May be worth a try, clear all formats then re-apply them!

**daddylonglegs** · 02-06-2013, 03:13 PM

Originally Posted by MP3265

..... The date is stored as a date......

How do you know that? If you apply different formats and nothing changes then that almost certainly means that it isn't a numeric value (dates are numeric values). Number formats (including date formats) can only be applied to numbers, applying them to text changes nothing.

How is the date derived? Is it the result of a formula, manually typed in or imported from somewhere else?