Excel Date Display Problem

May 1, 2012 at 07:29:22
Specs: Windows XP
Date formatted in Excel as 13-Dec-12 shows up Totally different as a five digit number. Itried to reformat the columns but it says that the format is set to read 13-Dec-12 but it reads as 41256 yet if I click on the cell it displays the correct date
Any ideas?

See More: Excel Date Display Problem

Report •

May 1, 2012 at 08:22:57
What you are seeing are "date serial numbers".

This site will explain how Excel stores internally, which will help explain what you are seeing - but not how to fix it. That comes later.


Excel often gets locked into certain formats when dates are involved, usually as a result of how the dates ended up in the spreadsheet. Most of the time the problem is caused when the dates are downloaded or copied from a website.

Unfortunately, there isn't a single solution to fixing these date problems since we can't tell what Excel is "confused" about internally.

One thing you could try is this:

Let's say your 5 digit numbers start in A1.

In a blank column enter this formula and drag it down:


This should display a date in your default date format.

Then do a Copy...PasteSpecial...Values on that column to eliminate the formulas and leave the dates.

Copy these dates over your column with the serial numbers and then delete the temporary column.

Good luck!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

May 28, 2012 at 00:59:22
I have a similar problem in a Gantt Chart that I'm working on. Everything is perfect except for the dates across the y value go from 9/14/11 - 5/6/13. I downloaded this as a template from Microsoft's template gallery but cannot get in contact with the author to find out how they formatted these dates.

The 'format' is fine but I need the y value to read April, May, June, July, August, September, October. I just want months, no days but if I had to define days, I guess I would go from the first of each month.

I have spent no less than 6 hours trying every way I can think of to change this and scouring the internet for help and I've only come up with solutions to simple y value problems that are unhelpful. The original author had the y values "linked" to a source but I cannot find this source. I've tried unchecking it, playing with the scale under 'formatting axis', and even deleting the column and creating a new one which really messes things up.

Is there anyone out there that I could email my file to in order to have a first hand look to see if they can figure out how to change this and then let me in on the secret in elementary school terms?

Report •

Related Solutions

Ask Question